XLOOKUP returns $29.00, the price for a Medium Blue Hoodie. Note the lookup_value in XLOOKUP is 1, since the logical expressions in lookup_array create an array of 1s and 0s. Read below for details. Note: in older versions of Excel, you can use the same approach with INDEX and MATCH.
Basic XLOOKUP
The most basic use of XLOOKUP involves just three arguments: Lookup_value is the value you are looking for, lookup_array is the range you are looking in, and result_array contains the value you want to return. There is no obvious way to supply multiple criteria.
Boolean Logic
This formula works around this limitation by using Boolean logic to create a temporary array of ones and zeros to represent rows matching all 3 criteria, then asking XLOOKUP to find the first 1 in the array. The temporary array of ones and zeros is generated with this snippet: Here we compare the item entered in H5 against all items, the size in H6 against all sizes, and the color in H7 against all colors. The initial result is three arrays of TRUE/FALSE values like this: The math operation (multiplication) automatically converts the TRUE FALSE values to 1s and 0s: After multiplication is complete, we have a single array like this: This is the array returned to XLOOKUP as the lookup_array. 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 our lookup_array contains only 1s and 0s, we set lookup_value to 1. At this point we can rewrite the formula like this: XLOOKUP locates the 1 in sixth row of the lookup_array, and returns the sixth value in the return_array (E5:E15), which is $29.00, the price of a Medium Blue Hoodie.
Array visualization
The arrays explained above can be difficult to visualize. The image below shows what is happening. Columns B, C, and D correspond to the data in the example, after being compared to the values in H5, H6, and H7. Column F is created by multiplying the three columns together. This is the array delivered to XLOOKUP as the lookup_array.
Alternative with concatenation
In simple cases like this example, you will sometimes see an alternative approach that uses concatenation instead of Boolean logic. The formula looks like this: The lookup_value is created by joining H5, H6, and H7 using concatenation: This results in the string “HoodieMediumBlue”. The lookup_array is created in a similar way, except we are now joining ranges: The return_array is supplied as a normal range, E5:E15: In essence, we are gluing together the values we need for criteria in the lookup_array, then looking for a value we created by joining together the Item, Size, and Color that we want to find. In this configuration, XLOOKUP returns $29.00 as before. This approach works in simple scenarios, but the Boolean Logic approach is far more flexible and powerful, so I recommend you use that method instead of concatenation. For an example of a problem you can’t solve with concatenation, see: XLOOKUP with complex multiple criteria.
INDEX and MATCH
XLOOKUP is only available in newer versions of Excel, but you can use the same technique with INDEX and MATCH, which will work in any version. The formula below uses INDEX and MATCH with Boolean logic to achieve the same result: Note: in Legacy Excel, this is an array formula and needs to be entered with Control + Shift + Enter. In newer versions of Excel that support dynamic array formulas, this formula will “just work”. For more details and a sample workbook, see: INDEX and MATCH with multiple criteria.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
