SUM formula with XLOOKUP with multiple arrays

Hi All

Hopefully this makes sense but I'm trying to create a rolled up view of live data which is extracted from multiple workbooks located in different Sharepoint areas (These can not be moved hence why I thought the 'Get Data' option would not be viable), however the format of these workbooks are all the same as these are from one template.

In the Summary workbook, I'm trying to SUM the totals for each month forecast and actuals from the Template workbook. This is the formula I have created so far but this is pulling incorrect data for my actuals as per the spreadsheet attached. I have added some values in the Template workbook for March & April 24 Forecast and Actuals and the results in the Summary workbook to show what I should expect but only getting the Forecast values correctly. I believe I need to include in this formula the array of either "Forecast" or "Actuals" unless there is a better option which I'm happy to utilise.

=SUM(XLOOKUP(D2,'path file[Template.xlsx]External Budget'!$M$17:$AJ$17,'path file[Template.xlsx]External Budget'!$M$29:$AJ$29)+XLOOKUP(D2,'path file[Template.xlsx]External Budget'!$M$32:$AJ$32,'path file[Template.xlsx]External Budget'!$M$44:$AJ$44))

Please do reach out if this does make sense or need more info.

Thanks in advance

For the purposes of assisting you, it would be so much easier for your helpers if you combined the two workbooks as tabs in one workbook.

It is enough to SAY that they are in two separate workbooks. However, for speed of assistance, a combined workbook in addition would help.

It's up to you - I'll have a look if you provide a combined version. Leave the uncombined ones there as well.

You've also removed the original files - I told you to leave them attached.

You also need to fix the links in the combined wrokbook to work with the other tab in the workbook. The idea is to make it as easy as you can for your helper (who is not being paid, by the way).

I'll have a look when you've got it all fixed.

I'd still use Get Data to consolidate all the data into a table in the summary workbook, then run your formulas off that.

Here is a formula that seems to do what you want:

Formula:

Please Login or Register  to view this content.

When the External Budget is moved to a separate workbook, the formula should update the references to that sheet to include the file path.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

I can not replicate the issue. I moved the External Budget sheet into a new workbook, then closed the new workbook, and the formula worked.
Without seeing a sample of your workbook that contains the External Budget sheet my thought is that "Actuals" in N18, P18 etc. may have an extra space behind the s.
as to C$2, when you compare the formulas in cells D4 and E4 you will see that in cell D4 the value_if_true portion of the IF based formula is directed to the correct month-year.
When the formula is dragged across from cell D4 to cell E4, C$2 becomes D$2 so the value_if_false portion of the formula is now directed to the correct month-year even though the value_if_true portion isn't. (I hope that makes sense)