You can use the following XLOOKUP formula in Excel to look up cells that meet multiple criteria:
=XLOOKUP(F2&G2&H2,A2:A13&B2:B13&C2:C13,D2:D13)
This particular formula will look for the cell in the range D2:D13 where the following criteria is all met:
- The value in cell range A2:A13 is equal to the value in cell F2
- The value in cell range B2:B13 is equal to the value in cell G2
- The value in cell range C2:C13 is equal to the value in cell H2
The following example shows how to use this formula in practice.
Example: XLOOKUP with Multiple Criteria in Excel
Suppose we have the following dataset that contains information about various basketball players:
Now suppose we would like to look up the points value for the player who meets all of the following criteria:
- Team = Cavs
- Position = Guard
- Starter = Yes
We can use the following formula to do so:
=XLOOKUP(F2&G2&H2,A2:A13&B2:B13&C2:C13,D2:D13)
We can type this formula into cell I2 and then press Enter:
This XLOOKUP formula is able to look up “Cavs” in the Team column, “Guard” in the Position column, “Yes” in the Starter column, and return the points value of 30.
We can check the original dataset and confirm that this is the correct points value for the player that meets all of these criteria:
Note that we used three criteria in this particular XLOOKUP formula, but you can use similar syntax to include as many criteria as you would like.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Find Duplicates Using VLOOKUP
Excel: How to Use VLOOKUP to Return All Matches
Excel: How to Use VLOOKUP to Return Multiple Columns