How to Use INDEX MATCH Lookup with Multiple Criteria in Excel [2 Cases]

When there are several matches for the lookup criteria, you can successfully do a lookup by using INDEX MATCH with multiple criteria. This is especially helpful if you need to locate a certain value in a dataset without unique keys or if you want to return results that satisfy several criteria. This approach is flexible and allows you to look up and return values even when there are multiple matches for the criteria you specify.

Case 1: Array Function for INDEX-MATCH Lookup with Multiple Criteria

Array formulas perform calculations on multiple cells at once, usually by processing arrays of values. They can return multiple results, and the result can spill over multiple cells. Array formulas often require special handling, such as pressing CTRL+SHIFT+ENTER to enter them correctly.

Syntax

=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))

Formula

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10) , 0))

Formula Breakdown

• This formula searches for and returns a value from the range C2:C10 using the INDEX and MATCH
• By comparing two criteria, it determines if the value in cell F1 matches any value in column A (A2:A10) and whether the value in cell F2 matches any value in column B (B2:B10).
• It creates two arrays of Boolean values for these conditions, and the MATCH function searches for the value 1 within the combined array, returning the position of the first TRUE

To use the INDEX MATCH formula to look up a value with multiple criteria for an array function, follow these steps:

1. Select cell F3.
2. Type the formula: =INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10) , 0))
3. Press CTRL+SHIFT+ENTER to insert the formula.

Array Visualization

Final Result

Finally, this formula retrieves the corresponding value from column C for the initial row that meets both criteria. In case no match is found, it returns an error.

Case 2: Non-Array Function for INDEX-MATCH Lookup with Multiple Criteria

Syntax

INDEX(return_range, MATCH(1, INDEX((criteria1=range1) * (criteria2=range2) * (..), 0, 1), 0))

Formula

=INDEX(C2:C10, MATCH(1, INDEX((F1=A2:A10) * (F2=B2:B10), 0, 1), 0))

Formula Breakdown

• This formula simultaneously determines if a value in cell F1 matches any of the items in a list (A2:A10) and if a value in cell F2 matches items in a different list (B2:B10).
• These checks are combined into a single list of “TRUE” and “FALSE.” To select the appropriate value from a different list (C2:C10), it first looks for the first “TRUE” in that list.

To perform a multi-criteria lookup using the INDEX MATCH formula for a non-array function, follow these steps:

1. Select cell F3.
2. Type the formula: =INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10) , 0))
3. Press CTRL+SHIFT+ENTER to insert the formula.

Final Result

This formula essentially achieves the same result as the previous formula.

Conclusion

In this article, I’ve explored two approaches for performing an Index-Match lookup with multiple criteria: using an array function or opting for a non-array function. If you choose the array function, be sure to execute it correctly by pressing CTRL+SHIFT+ENTER. Alternatively, for a non-array function, consider adding an Index function as a solution.

Frequently Asked Questions

Can I  INDEX MATCH two columns in Excel?

Yes, in Excel, you can use the INDEX and MATCH functions to perform a two-column lookup. This allows you to find a value at the intersection of a specific row and column based on matching criteria in two separate columns. The formula typically looks like this:

=INDEX(return_range, MATCH(lookup_value1 & lookup_value2, criteria_range1 & criteria_range2, 0)).

How to match data in Excel from 2 worksheets using INDEX MATCH?

To match data in Excel from 2 worksheets using INDEX MATCH:

1. On the destination sheet, select the cell where you want the matched data.
2. Enter the formula:

   =INDEX(Sheet2!return_range, MATCH(Sheet1!lookup_value, Sheet2!criteria_range, 0))

3. Replace ‘Sheet2’ with the name of the source sheet.
4. Adjust ‘return_range’, ‘lookup_value’, and ‘criteria_range’ based on your data.
5. Press Enter. This formula fetches data from Sheet2 based on matching criteria from Sheet1.

How do I find a value in Excel based on multiple criteria?

To find a value in Excel based on multiple criteria, use the combination of INDEX and MATCH functions. The formula typically looks like this:

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

1. Replace ‘return_range’ with the range containing the values you want to retrieve, and adjust ‘criteria1_range’, ‘criteria1’, ‘criteria2_range’, and ‘criteria2’ based on your specific criteria.
2. Press Ctrl+Shift+Enter to finalize the formula for multiple criteria lookup.

What is 2-way lookup in Excel?

A two-way lookup in Excel refers to a method of searching for a value at the intersection of a specific row and column in a table. It involves using both the row and column headers as criteria.

1. The INDEX and MATCH functions are commonly employed for two-way lookups.
2. The formula typically looks like this:

   =INDEX(return_range, MATCH(lookup_value1, criteria_range1, 0), MATCH(lookup_value2, criteria_range2, 0))

3. Replace ‘return_range’, ‘lookup_value1’, ‘criteria_range1’, ‘lookup_value2’, and ‘criteria_range2’ with the appropriate references in your dataset.

How do I compare two Excel files for matching data?

To compare two Excel files for matching data:

1. Open both Excel files.
2. Use conditional formatting to highlight differences.
3. Alternatively, use formulas like VLOOKUP, INDEX-MATCH, or IF statements to identify matching or differing data.
4. Consider using specialized tools or add-ins for more advanced and automated comparisons.
5. Pay attention to headers, and formatting, and ensure data alignment.
6. Manually review and resolve any discrepancies found during the comparison process.

How do I index match two lists?

To INDEX MATCH two lists in Excel:

1. Select the cell where you want the result.
2. Enter the formula: =INDEX(return_range, MATCH(lookup_value, criteria_range, 0)).
3. Replace ‘return_range’ with the range containing the values you want to retrieve.
4. Adjust ‘lookup_value’ and ‘criteria_range’ based on your specific lists.
5. Press Enter.
6. Drag the formula down to apply it to the entire list.
7. The result will be the matched values from the second list corresponding to the first list.