XLOOKUP help

Question

XLOOKUP help

Megan F 40

I have a master data tab called “Master FOF” and then one tab for each month of the year. I would like the data from Master FOF to be pulled into the month tabs by matching the names in column A on both tabs (the names match) and the fiscal year month orders from column B (Master tab) and cell A4 (month tabs). The data needs to be pulled into the correlating header columns on the month tabs. Some columns on the month tabs should be the totals of multiple columns on the Master FOF. Example: Purchases / Capital Calls and Sales / Distributions both have 3 columns each. All three cells for the month should be totaled together in one cell on month tabs.

I have tried many variations of an XLOOKUP formula, but they all keep erroring out, most commonly with the #NAME error. How can I fix this? I have linked to an excel example below.

https://limewire.com/d/WoqRQ#vCUZn4dhvF

0 comments

1 answer

Your answer

Answer 1

Hi Megan,

The #NAME? error usually indicates that Excel does not recognize part of the formula. This can happen for several reasons, such as using a function that is not supported in your version of Excel, referencing an invalid range or name, or having a typo in the formula.

In your scenario, XLOOKUP may not be the best option because you need to match two criteria:

The fund name in Column A.
The fiscal month in Column B of the Master FOF sheet, which should match the month value in cell A4 of the corresponding monthly tab.

Additionally, some fields on the monthly tabs require values from multiple columns in the Master FOF sheet to be combined into a single result, which makes a SUMIFS approach more suitable and easier to maintain.

You may want to use a formula such as:

=SUMIFS('Master FOF'!$E:$E,'Master FOF'!$A:$A,$A10,'Master FOF'!$B:$B,$A$4) +SUMIFS('Master FOF'!$G:$G,'Master FOF'!$A:$A,$A10,'Master FOF'!$B:$B,$A$4) +SUMIFS('Master FOF'!$I:$I,'Master FOF'!$A:$A,$A10,'Master FOF'!$B:$B,$A$4)

Alternatively, you can use SUMPRODUCT, for example:

=SUMPRODUCT(('Master FOF'!$A$3:$A$67=$A10)*('Master FOF'!$B$3:$B$67=$A$4)*{1,0,1,0,1},'Master FOF'!$E$3:$I$67)

Both formulas return a value of 170 in your sample file.

User's image

SUMIFS is likely to provide a more reliable solution than XLOOKUP for this workbook due to the need for multiple matching criteria and column aggregations.

However, if you would prefer to use XLOOKUP, could you please share the exact formula(s) you have tried and provide a bit more detail about the specific result you are trying to return? With that information, I can help identify why the formula is returning a #NAME? error and suggest the appropriate XLOOKUP syntax if it is suitable for your scenario.

If you have any questions or need further assistance, please feel free to share them in the comments on this post.

If the answer is helpful, please click 'Yes' and kindly upvote it.

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.