Excel Multi-Criteria Lookup Formula

Excel Formula using INDEX and MATCH Functions for Multiple Criteria Lookup

Formula:

=INDEX(return_range, MATCH(1, (criteria1_range=criteria1)*(criteria2_range=criteria2), 0))

Explanation:

• INDEX: Returns a value from a specified range based on the row and column number.
• MATCH: Searches for a specified value in a range and returns the relative position of that item.
• (criteria1_range=criteria1): Checks if the values in criteria1 range match the specified criteria1.
• (criteria2_range=criteria2): Checks if the values in criteria2 range match the specified criteria2.
• *: Acts as an AND operator to combine the criteria comparison results.
• MATCH(1, ... , 0): Returns the position of the first instance where both criteria are met.

Example:

If you have a table with data in columns A, B, and C, and you want to find a value in column D based on criteria in columns A and B, you can use the formula like this:

• Search value for Criteria1 is in cell E1
• Search value for Criteria2 is in cell F1

=INDEX(D:D, MATCH(1, (A:A=E1)*(B:B=F1), 0))

This formula will return the value from column D where both criteria specified in columns A and B are met based on the values in cells E1 and F1.