Xlookup with multiple lookup values to match with multiple lookup arrays

[ExcelNewbie2020]

Sirs,

Would it be possible to use xlookup to match multiple lookup values from multiple lookup arrays?

For example
Sheet 1, i have column A&Eas lookup value

Sheet 2 there is column C&D as my lookup array and column A as the return value..

Well, a possible solution would be to concatenate the column C&D of sheet 2 as helper column.. but im seeking solution that does not need a helper column..

Thanks

 

[hajiali]

You might be able to use index with multiple match. See the example in the like Here. If not please proved us with an example and post your excel using Xl2bb

 

[ExcelNewbie2020]

You might be able to use index with multiple match. See the example in the like Here. If not please proved us with an example and post your excel using Xl2bbthank you..

You might be able to use index with multiple match. See the example in the like Here. If not please proved us with an example and post your excel using Xl2bb

thank you very much..can you please resend the link..thanka

 

[hajiali]

Try Here

 

[AhoyNC]

It would help if you could post a small sample, but maybe one of these links will help. You might also want to look at the FILTER function.

XLOOKUP with multiple criteria The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29.00, the price for a Medium Blue Hoodie. Note the lookup_value in XLOOKUP is 1... exceljet.net xlookup with multiple lookups - Google Search

 

[Alex Blakenburg]

In case it helps:

=XLOOKUP( lookup_value, lookup_array, return_array)

Sheet1 (LookupUp Value)

Book2
	A	B	C	D	E	F	G
1	Fruit	Col2	Col3	Col4	Colour	Option 1 Concatenation	Option 2 Multiplication
2	Pear				Red	P01	P01
3
Sheet1

Cell Formulas
Range	Formula
F2	F2	=XLOOKUP(A2 & E2, Sheet2!$C$2:$C$10 & Sheet2!$D$2:$D$10, Sheet2!$A$2:$A$10)
G2	G2	=XLOOKUP(1, (Sheet2!$C$2:$C$10 = A2) * (Sheet2!$D$2:$D$10 = E2), Sheet2!$A$2:$A$10)

Sheet2 (Lookup Array)

Book2
	A	B	C	D
1	Code	Col2	Fruit	Colour
2	A01		Apple	Red
3	A02		Apple	Green
4	A03		Apple	Yellow
5	P01		Pear	Red
6	P02		Pear	Green
7
8
9
10
Sheet2