Please help - XLOOKUP formula works on 1 value only

[turner369]

Hi all,

I'm doing a spreadsheet where I'm using XLOOKUP to retrieve certain data with multiple criteria. By inputting the name in cell A5, cells B5 to E5 will retrieve the data from the spreadsheet below.

My formula is this

For Cell B5 =XLOOKUP(B4,G10:G37,XLOOKUP(A5,A10:A37,H10:H37))
For Cell C5 =XLOOKUP(C4,G10:G37,XLOOKUP(A5,A10:A37,H10:H37))
For Cell D5 =XLOOKUP(D4,G10:G37,XLOOKUP(A5,A10:A37,I10:I37))
For Cell E5 =XLOOKUP(E4,G10:G37,XLOOKUP(A5,A10:A37,I10:I37))

Now this is the most perplexing issue, the formula only works for T-shirts as seen below

However, when i sort by Category (Column G), the T-shirt formulas stop working and the Pants formula starts working

Tried sorting the other columns and there were no issues. Cell formats are also all the same

I have absolutely no clue why this is happening. Why does XLOOKUP work/not work when it is the exact same formula with only the lookup_value changed?

Please help!!

FYI i am using Microsoft Office Professional Plus 2019 with the ExcelDna.XFunctions Add-in for XLOOKUP function

 

[Alex Blakenburg]

Thanks for the feedback. Glad I was able to help.