Multi-criteria lookups in Excel allow you to perform complex searches based on multiple conditions. This is particularly useful when you need to match data from a table based on more than one criterion.
Use INDEX and MATCH with Multiple Criteria to Perform Complex Lookups
The combination of INDEX and MATCH functions can be used to perform lookups based on multiple criteria. This method is more flexible and powerful compared to the VLOOKUP function, especially when dealing with large datasets or when the lookup value is not in the first column.
Example
Below is an example demonstrating how to use the INDEX and MATCH functions with multiple criteria.
| Employee | Department | Salary |
|---|---|---|
| Alice | HR | 50000 |
| Bob | IT | 60000 |
| Charlie | Finance | 70000 |
| Diana | IT | 65000 |
| Edward | HR | 55000 |
| Fiona | Finance | 72000 |
| George | IT | 64000 |
| Hannah | HR | 53000 |
| Ian | Finance | 71000 |
| Jane | IT | 63000 |
Exercise
- In the "Lookup Employee" (cell A15) and "Lookup Department" (cell B15) cells input the criteria for the lookup.
- Use the INDEX and MATCH functions to find the corresponding salary based on the entered criteria.
Solution
In the embedded sheet, the formula for finding the salary based on the lookup criteria is provided in the "Resulting Salary" column. Here is how the solution is structured:
The formula for Resulting Salary:
=INDEX(C2:C11, MATCH(1, (A2:A11=A15)*(B2:B11=B15), 0))Explanation
- INDEX Function:
=INDEX(C2:C11, ...)specifies the range to return a value from, in this case, the Salary column (C2).
- MATCH Function with Multiple Criteria:
MATCH(1, (A2:A11=A15)*(B2:B11=B15), 0)searches for the row where both criteria are met:(A2:A11=A15)checks if the Employee column matches the lookup employee in cell A15.(B2:B11=B15)checks if the Department column matches the lookup department in cell B15.- The multiplication of these conditions creates an array of 1s and 0s, where 1 represents rows that match both criteria.
MATCH(1, ..., 0)finds the position of the first 1 in this array, indicating the row where both criteria are satisfied.
Using these formulas in your embedded sheet ensures that the salary is dynamically retrieved based on the specified criteria.