Hi - Dave here.
One of the great new functions in Excel is XLOOKUP, a modern replacement for the famous VLOOKUP function.
But how do you use XLOOKUP with multiple criteria?
At a glance, this seems like a tricky problem because XLOOKUP only accepts one value for
lookup_value and one value for
lookup_array. How can we tell XLOOKUP to use values in
multiple columns?
The most flexible approach is to use Boolean logic. Boolean logic is a technique of building formulas with simple logical expressions that return TRUE or FALSE.
You can see this approach below, where the formula in cell H8 is:
=XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15)
[
download the workbook and read the full explanation]
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. Both approaches are included in the article. Download the workbook and have a look.
Gift idea
Need a gift for the Excel lover in your life? We have a nice
laminated card with over 200 Excel shortcuts for both Windows and Mac. We now ship worldwide to many countries.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer paid
video training.
Have a nice weekend!
Dave
