# Xlookup with multiple lookup values to match with multiple lookup arrays



## ExcelNewbie2020 (Dec 16, 2022)

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 (Dec 16, 2022)

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 (Dec 16, 2022)

hajiali said:


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





hajiali said:


> 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 (Dec 16, 2022)

Try Here


----------



## AhoyNC (Dec 16, 2022)

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 (Dec 17, 2022)

In case it helps:

=XLOOKUP( lookup_value, lookup_array, return_array)

Sheet1 (LookupUp Value)

Book2ABCDEFG1FruitCol2Col3Col4ColourOption 1 ConcatenationOption 2 Multiplication2PearRedP01P013Sheet1Cell FormulasRangeFormulaF2F2=XLOOKUP(A2 & E2,
                    Sheet2!$C$2:$C$10 & Sheet2!$D$2:$D$10,
                    Sheet2!$A$2:$A$10)G2G2=XLOOKUP(1,
                    (Sheet2!$C$2:$C$10 = A2) * (Sheet2!$D$2:$D$10 = E2),
                    Sheet2!$A$2:$A$10)

Sheet2 (Lookup Array)

Book2ABCD1CodeCol2FruitColour2A01AppleRed3A02AppleGreen4A03AppleYellow5P01PearRed6P02PearGreen78910Sheet2


----------

