This post may contain affiliate links. Please read my disclosure for more info:

Index Match Match in Excel: 2 dimension lookup

Index Match Match Thumb

Index Match Match

In this post, I want to cover one of the most powerful lookup functions available in Excel, INDEX MATCH MATCH.  Actually, to call it a function is poor terminology, as it is three functions used together within a single formula.

Before digging into this formula, let’s look at when to use it.

INDEX MATCH MATCH - Example

The screenshot above shows the 2016 Olympic Games medal table.  The list in Column A shows the medal count for each country in Columns B through E.  These types of table are common, with a unique list of records on the left and a unique list of categories along the top.

As a simple example, how could we use a formula to retrieve the number of Bronze medals received by Japan? There are multiple formula options at our disposal, but by far the simplest and most powerful is INDEX MATCH MATCH.

If you are familiar with the INDEX MATCH function, this post should not be too much of a leap for you, as the principles are the same.  If you are not familiar with it, don’t worry, I will explain everything.

 

Applying the INDEX MATCH MATCH formula

To understand how this formula works we will consider each function individually, then build up to the combined formula.

 

MATCH

The MATCH function finds the position of an item within a list.  Using our Olympic Games example, if we looked for “Japan (JPN)” from the country list in Column A, it would return 6, as it is the 6th item in the list.

The syntax for this function is as follows:

=MATCH(lookup_value, lookup_array, [match_type])
    • lookup_value – the value to be found.  Can be a number, text, logical value (i.e., true/false) or a reference to a cell containing a number or text.
  • lookup_array: the range of cells from which to find the lookup_value.
  • [match_type]: A value of 1, 0 or -1, which tells Excel how the lookup calculation should be performed. The square brackets indicate this argument is optional. However, if omitted it will assume the match_type value should be 1.  It is not necessarily a safe assumption for Excel to be making, so always insert an argument, rather than letting the default apply.

The match_type is important, as it can affect the result of the calculation in unexpected ways.

  • 1 = the function will return the largest value which is less than or equal to the lookup_value.  To calculate correctly, the values in the lookup_array must be in ascending order.
  • -1 = the function will return the smallest value which is greater than or equal to the lookup value.  To calculate correctly, the values in the lookup_array must be in descending order.
  • 0 = the function will return the first exact match found.  The values in the lookup_array can be in any order.

If you are unsure which option to use, I recommend using 0 for an exact match, which will return a value (if it is found) irrespective of the order of data in the lookup_array.

Example of MATCH function

The formula in cell H5 is:

=MATCH(H3,A2:A88,0)
  • H3 = Japan (JPN) – the lookup_value.
  • A2:A88 = list of countries – the lookup_array
  • 0 = an exact match – the match_type

Japan (JPN) is the 6th country in the list, so the MATCH function returns 6.

The MATCH function works equally well with rows or columns.  Using this same function for the columns, we are also able to retrieve the position of the word “Bronze”.

Example of MATCH function across columns

The formula in cell H10 is:

=MATCH(H8,B1:E1,0)
  • H8 = Bronze – the lookup_value.
  • B1:E1 = list of medals across the columns – the lookup_array
  • 0 = an exact match – the match_type

Bronze is the 3rd medal type in the selected columns, so the MATCH function returns 3.

 

INDEX

The INDEX function calculates the reference to a cell based on a given row or column position.  It sounds much harder to understand than it is.  For example, if INDEX were calculating the 7th cell within the range A5:A15, the result would be Cell A11.  Note, it would not be A12, as A5 counts as the 1st cell in the range.

The syntax for the INDEX function is as follows:

=INDEX(array, row_num, [column_num])
  • array – the range of cells from which to find the position
  • row_num – the nth row position to be found in the array.  If omitted, the column_num is required.
  • column_num – the nth column position to be found in the array.  Can be used in conjunction with the row_num or by itself if the row_num is omitted.

We will build this function up one piece at a time so that you can see the impact.  Initially, we will omit the column_num.

Example of INDEX MATCH function

The formula in Cell H12 is:

=INDEX(D2:D88,H5)
  • D2:D88 = the range of Bronze medal results – the array.
  • H5 = 6 – the row_num (calculated by the MATCH function above).

The 6th row in the Range D2:D88 calculates as Cell D7, so the result in cell H12 is 21 (the same as Cell D7)

The INDEX function’s ability to return a cell reference is an important distinction, which we will consider later in this post.

In the example above, we selected the Cells D2:D88 (the Bronze medal cells) as the array. Therefore the formula could only calculate values from that those cells.  By incorporating the column_num into the INDEX function we can look up across both the rows and columns to pinpoint an exact cell for any country and any medal type.

Example of INDEX MATCH MATCH function

The formula in cell H12 is:

=INDEX(B2:E88,H5,H10)
  • B2:E88 = the range of results for the whole medal table – the array.
  • H5 = 6 – the row_num of “Japan (JPN)”
  • H10 = 3 – the column_num of “Bronze”

The 6th row and 3rd column of the array calculates as Cell D7, which is equal to 21.

 

Nesting the functions inside each other

The good news is that whilst there are three functions at work, we can place all the functions into a single formula.

INDEX MATCH MATCH with a single formula

The formula in H14 is:

=INDEX(B2:E88,MATCH(H3,A2:A88,0),MATCH(H8,B1:E1,0))

It might look a bit scary, but now that we’ve built it up in stages, I hope you’ll agree that it’s not so bad after all.

 

Common INDEX MATCH MATCH errors

As it is three functions combined into a single formula, troubleshooting error messages can seem a bit daunting.  But there are some common errors which you should check for first.

 

Incorrect or missing match_type in the MATCH function

Using the wrong match_type, or excluding the match_type from the MATCH function can cause several errors:

Returns a value, but it is not correct.
This error occurs typically when the match_type is 1 or -1, but the data is not sorted in ascending or descending order respectively.

Returns an #N/A! error
This error commonly occurs when:

  • using a match_type of 0 and the lookup_value is not found in the lookup_array
  • the lookup_array is lower than the lookup_value when using a match_type of -1
  • using a match_type of 1 and the first value in the lookup_array is higher than the lookup_value
  • the lookup_value or lookup_array contains values which are formatted as text, rather than numbers (or vice versa)
  • the lookup_value or lookup_array contains leading or trailing spaces which are unseen (try using the TRIM function or removing the spaces manually to correct this issue).

 

The MATCH lookup_array and the INDEX array are not the same size

A #REF! error can occur when a MATCH is found, but the INDEX array is not big enough to include that row.  For example, if there are 10 cells in the MATCH lookup_array, but only 5 cells in the INDEX array a #REF! error will be returned for any MATCH in the 6th to 10th position.

 

The MATCH lookup_array or INDEX array are referencing the wrong cells

Depending on the specific circumstances, this issue can cause incorrect results or an #N/A! error.  It usually occurs when copying formulas where the cell references are not frozen (i.e., they are not absolute cell references containing the $ symbol).

 

If you still can’t find the cause of the error

If after trying these options, you are still unable to find the cause of the error, try building up the formula in individual pieces as in the examples above.

 

INDEX MATCH MATCH tricks

Many Excel experts will advocate INDEX MATCH as better than VLOOKUP, equally INDEX MATCH MATCH is better than VLOOKUP MATCH or SUMPRODUCT in this scenario.  This formula provides more flexibility, which enables the use of the following Excel magic tricks.

 

Return a value above, below, left or right of the matched value

Using our scenario we could find out how many gold medals the country above (or below) received.

Find the value above the value

The formula in H7 uses the INDEX MATCH function to find the country above “Japan (JPN)” just by subtracting 1 from the result of the MATCH function.

=INDEX(A2:A88,MATCH(H3,A2:A88,0) - 1)

The formula in H9 will find the number of gold medals the country above received, using the same method of subtracting 1 from the result of the MATCH function.

=INDEX(B2:E88,MATCH(H3,A2:A88,0) - 1,MATCH(H5,B1:E1,0))

 

Return the cell address, row or column of the matched value

If working with a lot of data, it can be difficult to know from which cell the returned value has come.  The INDEX function is magic, as it can return the cell address, row number or column number of that cell.

Following on from our last example, how do we know which cell is returning the value in Cell H9 (Germany’s Gold medals)?

Cell Address from INDEX

Cell H11 and H17 both contain the answer but derived in different ways.

The formula in H11 uses the CELL function to return the address:

=CELL("address",INDEX(B2:E88,MATCH(H3,A2:A88,0) - 1,MATCH(H5,B1:E1,0)))

The formula in H13 uses the ROW function to return the row number:

=ROW(INDEX(B2:E88,MATCH(H3,A2:A88,0) - 1,MATCH(H5,B1:E1,0)))

The formula in H15 uses the COLUMN function to return the column number:

=COLUMN(INDEX(B2:E88,MATCH(H3,A2:A88,0) - 1,MATCH(H5,B1:E1,0)))

Cell H17 uses the ADDRESS function combined with the ROW and COLUMN functions to return the cell address (Formula broken into multiple lines to fit onto the page):

=ADDRESS(
ROW(INDEX(B2:E88,MATCH(H3,A2:A88,0) - 1,MATCH(H5,B1:E1,0))),
COLUMN(INDEX(B2:E88,MATCH(H3,A2:A88,0) - 1,MATCH(H5,B1:E1,0))))

 

Create a dynamic range

As we have seen, the INDEX function returns a cell address, therefore it is possible to create a dynamic range.

Adapting our scenario slightly, let’s say we wanted to know how many Gold and Silver medals were won by all the countries from Great Britain (GBR) to Italy (ITA).  Simple, just add the SUM function to what we already know.

Calculating a dynamic range

The formula in Cell H7 is:

=SUM(INDEX(B2:E88,MATCH(H3,A2:A88,0),MATCH(H5,B1:E1,0)):
INDEX(B2:E88,MATCH(I3,A2:A88,0),MATCH(I5,B1:E1,0)))

Woah – that looks pretty scary, right?   It is so long I had to insert a line break so that it could fit on the page.  But let’s take a close look.  I’ll even color code to make it easier:

=SUM(INDEX(B2:E88,MATCH(H3,A2:A88,0),MATCH(H5,B1:E1,0)):
INDEX(B2:E88,MATCH(I3,A2:A88,0),MATCH(I5,B1:E1,0)))

This is the formula we’ve used before, but using Great Britain (GBR) and Gold as the criteria.  This would return a cell reference of B3.

The same formula again, but using Italy (ITA) and Silver as the criteria, returning C10 as the cell reference.

A standard SUM function, with the range based on the results of the two INDEX functions.  Note the use of the colon ( : ) between the two INDEX functions, it is this which turns to two calculations into a single contiguous range.

The long formula calculates down to the following simple formula:

=SUM(B3:C10)

See, that wasn’t so scary after all.

 

Using an Array formula to match multiple criteria in a row or column

The 2012 Olympic Games medal table has now been added to the 2016 data.  Our country list is no longer unique, each name appears twice, once in 2012 and once in 2016.

When combining the country name with the year, it is possible to make the records unique again.  We could use a helper column, but instead of adding an extra column we will use an Array formula.

The screenshot below shows that by using the country name, year and medal type it is possible to retrieve a value.

INDEX MATCH MATCH array formula

The formula in cell I9 is:

{=INDEX(C2:F174,MATCH(I3&"-"&I5,A2:A174&"-"&B2:B174,0),MATCH(I7,C1:F1,0))}

This is an array formula so cannot be entered the normal way.  The curly braces { and } are not part of the formula; these are added by Excel when pressing Ctrl + Shift + Enter.

The lookup_value in the first MATCH function has been combined with a hyphen in between.

The lookup_array has been joined with a hyphen in between, turning it into an array formula.

It is possible to use multiple criteria in the column headings too, meaning INDEX MATCH MATCH can lookup a value from multiple criteria in the rows and columns.

 

Conclusion

Whilst there are alternative ways to perform a 2-dimensional lookup, none of those options are as powerful, as understandable, or as fast as using INDEX MATCH MATCH in Excel.  This formula is so useful that it needs to be within your Excel toolkit.

Leave a Reply

Your email address will not be published. Required fields are marked *