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
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 |