Excel Formula: Values for XLOOKUP are not coming through consistently

[lopsan]

Hi Everyone!

First time posting here.. so wish me luck

So, I am working on creating charts and what not for my job. I've already built a Dashboard-like workbook which looks spectacular btw... Anyways, I am only having issues with one particular section of my Dashboard, when using XLOOKUP. For some reason, my formula does give me data correctly when switching between 'Sales List', 'Year' and 'Months'... However, when the Month is 'Nov' - for November, the formula results in an Error.

I've tried what I know, but I have not been able to find a work-around, or a way to fix this. So I am coming to you all for help :D

I've attached a Sample Excel Workbook - the Data there has been modified and scrambled to protect my companies data, however, I replicated the tables and the formulas - which I still get the same issue.

Description of Issue:

When selecting the following 'filters' - (Any Selection Sales), 2020, Dec.

The table I have with the XLOOKUPs, results in this:

Month ACV
Aug #N/A
Sep $2,933,280
Oct $9,940,320
Nov #N/A
Dec $8,277,120

We see that both for Aug and Nov I get an Error - However, Aug is fine since Aug does not exist within the range selected for the XLOOKUP... but Nov is. When I have the above filters, Nov provides no data. However, when the filters change to - (Any Selection Sales), 2020, Nov, I get the following:

Month ACV
Jul #N/A
Aug #N/A
Sep $2,933,280
Oct $9,940,320
Nov $4,989,600

We see that Aug and Jul are now showing Errors - which is fine. But Nov now returns the correct value.

Question: Why does Nov return value on the second instance, but not on the first?

Thanks in advanced!

[Glenn Kennedy]

Hi. The way you have created those dates is a bit messy. They have caused your formulae to be FAR more complicated than was needed. Use real dates and format them to look like months and (I suggest) years, for clarity. Those cosmetic changes are shaded purple.

L37, copied down:
=INDEX($I$9:$AF$24,MATCH($K37,$H$9:$H$24,0),MATCH($K$31,$I$8:$AF$8,0))

M37, copied down:
=3*INDEX($Y$34:$AN$57,MATCH($K$31,$X$34:$X$57,0),MATCH(K37,$Y$33:$AN$33,0))