IF and XLOOKUP

[ekhawaja]

Hello Team,

Looking for a formula to enter in COL C sheet 2 that first checks for a match of col A with col D (sheet 1), if that is true, then it does a lookup of col B in Col H (sheet 2) and returns the corresponding col Z value.

Thanks in advance.

SHEET 1	SHEET 2
COL D	COL H	COL Z	COL A	COL B	COL C
223	a	apple	265	c	orange
223	b	apple	223	a	apple
265	c	orange	224	f
224	e	peach	220	g
223	f	banana	265	c	orange

 

[hagia_sofia]

Hello, if I understand it correctly then maybe something like:

=IF(SUM(--(A2=SHEET1!$D$2:$D$6)),XLOOKUP(B2,$H$2:$H$6,$Z$2:$Z$6),"")

 

[ekhawaja]

Hello, if I understand it correctly then maybe something like:

=IF(SUM(--(A2=SHEET1!$D$2:$D$6)),XLOOKUP(B2,$H$2:$H$6,$Z$2:$Z$6),"")

thank you for the reply, the formula seems to be giving me blank values instead of the correct answer. Here is how i have it entered.


=IF(SUM(--(D28467=[Book3.xlsx]Sheet1!$A:$A),XLOOKUP(H28467,[Book3.xlsx]Sheet1!$I:$I,[Book3.xlsx]Sheet1!$AG:$AG)),"")

 

[ekhawaja]

the formula worked, but I noticed some of the results returned were incorrect. For example , the below red answer should have been orange, not apple. Sorry if this is confusing.

 

[hagia_sofia]

Thanks for the feedback. The question is what is decisive for the formula to return "orange"? As there are two values of "a", XLOOKUP will return the first match it will find. For it to return "organge" it would seemigly correspond to the combination of "265" and "a" - but does "265" on SHEET1 have any relation to the values on SHEET2?

 

[ekhawaja]

Thanks for the feedback. The question is what is decisive for the formula to return "orange"? As there are two values of "a", XLOOKUP will return the first match it will find. For it to return "organge" it would seemigly correspond to the combination of "265" and "a" - but does "265" on SHEET1 have any relation to the values on SHEET2?

the "if statement" should first check if 265 matches, and if it does, it should return the "a" that corresponds to 265, not the "a" that corresponds to 223.

 

[ekhawaja]

Any help would be greatly appreciated.

 

[Alex Blakenburg]

How about:

Book4
	A	B	C
1	COL A	COL B	COL C
2	265	c	orange
3	223	a	apple
4	224	f
5	220	g
6	265	c	orange
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=XLOOKUP(1, (Sheet1!$A$2:$A$10000=A2)*(Sheet1!$B$2:$B$10000=B2), Sheet1!$C$2:$C$10000,"")

 

[ekhawaja]

How about:

Book4
	A	B	C
1	COL A	COL B	COL C
2	265	c	orange
3	223	a	apple
4	224	f
5	220	g
6	265	c	orange
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=XLOOKUP(1, (Sheet1!$A$2:$A$10000=A2)*(Sheet1!$B$2:$B$10000=B2), Sheet1!$C$2:$C$10000,"")

THANK YOU SO MUCH @Alex Blakenburg ......THIS FORMULA WORKED!! and it is giving correct answers. Thanks a lot!

 

[ekhawaja]

Hello, if I understand it correctly then maybe something like:

=IF(SUM(--(A2=SHEET1!$D$2:$D$6)),XLOOKUP(B2,$H$2:$H$6,$Z$2:$Z$6),"")

thank you @hagia_sofia for your help too,