How to Perform a Lookup with Multiple Criteria Using the Match and Index Functions in Excel

The Match Function Nested in Index Function is a powerful combination of two Excel functions that can perform a lookup based on multiple criteria in rows and columns. This article will explain the basic theory, the procedures, and a comprehensive example with real data.

The Index Function returns a value from a range based on a given row and column number. The syntax is:

=INDEX (range, row_num, [column_num])

Where:

• range is the range of cells from which to return a value.
• row_num is the row number in the range.
• column_num is the optional column number in the range.

The Match Function returns the relative position of a value in a range that matches a given criterion. The syntax is:

=MATCH (lookup_value, lookup_range, [match_type])

Where:

• lookup_value is the value to look for in the range.
• lookup_range is the range of cells to search for the value.
• match_type is the optional number that specifies how to match the value. It can be 0 for exact match, 1 for less than or equal to, or -1 for greater than or equal to.

By nesting the Match Function inside the Index Function, we can create a dynamic lookup formula that can find a value based on multiple criteria in rows and columns. The general formula is:

=INDEX (range, MATCH (row_lookup_value, row_lookup_range, 0), MATCH (column_lookup_value, column_lookup_range, 0))

Where:

• range is the range of cells from which to return a value.
• row_lookup_value is the value to look for in the row.
• row_lookup_range is the range of cells to search for the row value.
• column_lookup_value is the value to look for in the column.
• column_lookup_range is the range of cells to search for the column value.

Procedures

To use the Match Function Nested in Index Function in Excel, follow these steps:

1. Identify the range of cells from which to return a value. This is the range argument of the Index Function.
2. Identify the value to look for in the row. This is the row_lookup_value argument of the Match Function.
3. Identify the range of cells to search for the row value. This is the row_lookup_range argument of the Match Function.
4. Identify the value to look for in the column. This is the column_lookup_value argument of the Match Function.
5. Identify the range of cells to search for the column value. This is the column_lookup_range argument of the Match Function.
6. Enter the formula in a cell and press Ctrl + Shift + Enter to complete it as an array formula.

Example

Let’s say we have a table of sales data for different products in different regions and months, as shown below:

We want to find the sales figure for product B in the South region in February. We can use the Match Function Nested in Index Function to do this. The formula is:

=INDEX (D2:D9, MATCH ("South"&"Feb"&"B", A2:A9&B2:B9&C2:C9, 0))

Where:

• D2:D9 is the range of cells from which to return a value.
• "South"&"Feb"&"B" is the value to look for in the row, concatenated with the ampersand (&) operator.
• A2:A9&B2:B9&C2:C9 is the range of cells to search for the row value, concatenated with the ampersand (&) operator.
• 0 is the match type for exact match.

The formula returns 280, which is the correct sales figure for product B in the South region in February.