Lookup 3 conditions on different sheet

[colzre]

Hello experts,

I am trying to build a lookup function (any other I would be happy too) to consider 3 conditions from Sheet2 and return to Sheet1 if all of them meet.

Basically if all conditions PROYECT, CONCEPT AND VAT are found, then return VAT TO RETURN on cell R9. Blank cells would return as zero value. I used xlookup with no success.

Any idea to make it work?

Many thanks beforehand!


Regards,

John

Book1.xlsx
	K	L	M	N	O	P	Q	R	S
6
7
8	PROYECT	CONCEPT	VAT					VAT TO RETURN
9	Austria	IT	Invoice					#VALUE!
10	France	Administration
11	Brazil	Travels	Invoice
12
13
Sheet1

Cell Formulas
Range	Formula
R9	R9	=XLOOKUP(K9,Sheet2!E6:E8,XLOOKUP(L9,Sheet2!F6:F8,XLOOKUP(M9,Sheet2!G6:G7,Sheet2!H6:H8)))

Book1.xlsx
	E	F	G	H
2
3
4	PROYECTS	CONCEPT	VAT	VAT TO RETURN
5
6	Austria	IT	Invoice	20
7	France	Administration		20
8	Brazil	Travels		7
Sheet2

 

[Cubist]

Maybe this?

Book1
	J	K	L	M	N	O	P	Q	R	S
6
7
8		PROYECT	CONCEPT	VAT					VAT TO RETURN
9		Austria	IT	Invoice					20
10		France	Administration
11		Brazil	Travels	Invoice
12
13
Sheet1

Cell Formulas
Range	Formula
R9	R9	=LET(lookup,BYROW(Sheet2!$E$6:$G$8,LAMBDA(r,CONCAT(r))),return,Sheet2!$H$6:$H$8,XLOOKUP(K9&L9&M9,lookup,return,0,0))

 

[awoohaw]

Or try this: This is not a single cell formula and needs to be copied down.
You can change the "Not Found" in the formula to a zero.

ExcelQuestions80.xlsx
	K	L	M	N	O	P	Q	R
1
2
3
4
5
6
7
8	PROYECT	CONCEPT	VAT					VAT TO RETURN
9	Austria	IT	Invoice					20
10	France	Administration						20
11	Brazil	Travels	Invoice					Not Found
12								Not Found
Sheet1

Cell Formulas
Range	Formula
R9:R12	R9	=XLOOKUP( K9&L9&M9, Sheet2!$A$5:$A$7 & Sheet2!$B$5:$B$7 & Sheet2!$C$5:$C$7, Sheet2!$D$5:$D$7,"Not Found",0)

 

[colzre]

Thank you both.

Sorted with your help!!

Appreciated

 

[awoohaw]

My pleasure, happy to help. Best Wishes!