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

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(H10:H37,MATCH(A5&"|"&B4,A10:A37&"|"&G10:G37,0))

This will need to be confirmed with Ctrl Shift Enter, rather than just enter.

 

[turner369]

Hi & welcome to MrExcel.
How about

=INDEX(H10:H37,MATCH(A5&"|"&B4,A10:A37&"|"&G10:G37,0))

This will need to be confirmed with Ctrl Shift Enter, rather than just enter.

Yes, this does work!! thank you so much!! but would you happen to know why my xXLOOKUP formula didn't work? I'm still quite perplexed on why the order of the category decides whether the formula works or not

 

[Fluff]

Assuming that the add-in works in the same way as a normal Xlookup, your formula does not make much sense.
With this

=XLOOKUP(C4,G10:G37,XLOOKUP(A5,A10:A37,H10:H37))

the part in red will return the first cell that matches A5, which in your example is A10. That will then feed into the outer xlookup which becomes

=XLOOKUP(C4,G10:G37,H10))

so unless C4 is found in row 10 it will never work.

 

[turner369]

Assuming that the add-in works in the same way as a normal Xlookup, your formula does not make much sense.
With this

=XLOOKUP(C4,G10:G37,XLOOKUP(A5,A10:A37,H10:H37))

the part in red will return the first cell that matches A5, which in your example is A10. That will then feed into the outer xlookup which becomes

=XLOOKUP(C4,G10:G37,H10))

so unless C4 is found in row 10 it will never work.

thank you for the answer! guess i need some brushing up on my formulation

 

[Alex Blakenburg]

thank you for the answer! guess i need some brushing up on my formulation

Assuming the Add-In works exactly the same as the 365 version.

Then this is how your xlookups should look.

20210818 XLookup AddIn.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3		Quantity		Size
4	Name	T-Shirts	Pants	T-Shirts	Pants
5	Jose	3	1	L	32
6
7
8
9	Name	Code	Number	Date Joined	Date Resigned	Title	Category	Quantity	Size
10	Jose						T-Shirts	3	L
11	Jose						Pants	1	32
12	Ain						T-Shirts	4	S
13	Ain						Pants	0
14
Sheet1

Cell Formulas
Range	Formula
B5:C5	B5	=XLOOKUP(1,($G$10:$G$37=B$4)*($A$10:$A$37=$A5),$H$10:$H$37)
D5:E5	D5	=XLOOKUP(1,($G$10:$G$37=D$4)*($A$10:$A$37=$A5),$I$10:$I$37)

 

[turner369]

Assuming the Add-In works exactly the same as the 365 version.

Then this is how your xlookups should look.

20210818 XLookup AddIn.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3		Quantity		Size
4	Name	T-Shirts	Pants	T-Shirts	Pants
5	Jose	3	1	L	32
6
7
8
9	Name	Code	Number	Date Joined	Date Resigned	Title	Category	Quantity	Size
10	Jose						T-Shirts	3	L
11	Jose						Pants	1	32
12	Ain						T-Shirts	4	S
13	Ain						Pants	0
14
Sheet1

Cell Formulas
Range	Formula
B5:C5	B5	=XLOOKUP(1,($G$10:$G$37=B$4)*($A$10:$A$37=$A5),$H$10:$H$37)
D5:E5	D5	=XLOOKUP(1,($G$10:$G$37=D$4)*($A$10:$A$37=$A5),$I$10:$I$37)

Hey Alex, thanks for the tip. Can I ask why is the lookup value 1?

 

[Alex Blakenburg]

Because the next set of parameters are
(array=value)*(array=value)
These return sets of trues and falses.
These when multiplied together turn into 1s and 0s.
Let me know I haven't explained it well.

 

[Alex Blakenburg]

See if this helps, I adjusted the range to only look are 4 rows to make it more manageable.

 

[turner369]

See if this helps, I adjusted the range to only look are 4 rows to make it more manageable.

View attachment 45182

ahhh, I totally get it now, thanks so much truly! i really appreciate this