Using INDEX MATCH MATCH

If you use INDEX MATCH frequently in your worksheets, you may be surprised to learn about an even more powerful version of the formula: INDEX MATCH MATCH. This variant allows you to look up values across rows and columns simultaneously. Let's take a look at how this new construction works.

Defining the problem

The following spreadsheet lists two metrics — Cookie packs sold and Revenue — for SnackWorld by month from January through May.

We know how to run an INDEX MATCH on either one of these columns to pull a specific metric by month, using the standard formula:

=INDEX(range, MATCH(lookup_value, lookup_range, match_type))

But, what if we also want to add a flexible input for the column as well as the row? Say, for example, we want to tell Excel dynamically, in the formula, that we should pull the January data for Cookie packs sold or the April data for Revenue?

The INDEX function with both row and column arguments

Previously, we've only seen the INDEX function used with a single row or column for the range, along with one argument, like so:

=INDEX(range, row_or_column)

The output might look something like this:

=INDEX(C2:C7, 4)
Output: 19

But, it turns out that INDEX has the capability to take an additional, optional argument — for a column number. The expanded formula looks like this:

=INDEX(range, row_number, column_number)

When we use this form of INDEX, the range can be an entire, two-dimensional table rather than a single row or column. The row_number argument specifies how many rows into the table to look, and the column_number argument does the same for columns.

Take a look at the following example, in which we run INDEX with two parameters on our SnackWorld metrics table:

=INDEX(B2:D7, 4, 2)
Output: 19

Given a table in the range B2:D7, the function first looks 4 rows down to the March row. It then looks 2 columns across to the Cookie packs sold column. It returns the cell that it finds at the intersection of these two lookups — which contains the number 19.

Adding MATCH

By now, you've probably guessed what we're going to do next: add in a MATCH formula to both the first and second arguments of INDEX, allowing us to match against both a row and column header. Let's try it out with a formula designed to pull a given metric for a particular month based on dynamic input cells:

=INDEX(B2:D7, MATCH(G2, B2:B7, 0), MATCH(G3, B2:D2, 0))
Output: 456

First, we run an INDEX on the entire table range B2:D7. The first argument to this function is the row_number, for which we pipe in a MATCH function that matches the phrase in cell G2 ("March", in this example) against the cell range B2:B7. The second argument is the column_number, for which we use a MATCH function that matches the phrase in cell G3 ("Revenue", in this example) against the cell range B2:D2. The result is 456, the value at the intersection of the March row and Revenue column.

INDEX MATCH MATCH with wildcards

You can also use INDEX MATCH MATCH with wildcards to look up based on a partial phrase or string. Take a look at our tutorial on wildcards in Excel for more information.

Comments