At a glance, this seems like a difficult problem because XLOOKUP only has one value forlookup_valueandlookup_array.
How can we configure XLOOKUP to consider values inmultiplecolumns?
This approach is explained below.
There is no obvious way to supply multiple criteria.
Notice, the sixth value in the array is 1.
This corresponds to the sixth row in the data, which contains a Medium Blue Hoodie.
Because ourlookup_arraycontains only 1s and 0s, we set thelookup_valueto 1.
This is $29.00, the price of a Medium Blue Hoodie.
Array visualization
Thearraysexplained above can be difficult to visualize.
The image below shows the process.
Column F is created by multiplying the three columns together.
This is the array delivered to XLOOKUP as thelookup_array.
In this configuration, XLOOKUP returns $29.00 as before.
For an example of a problem that cannot be solved with concatenation, seeXLOOKUP with complex multiple criteria.
In newer versions of Excel that supportdynamic array formulas, this formula will work seamlessly.
For more details and a sample workbook, seeINDEX and MATCH with multiple criteria.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you could use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
