Excel Tip of the Week #201 - INDEX MATCH redux

Hello and welcome back to the Excel Tip of the Week!  This week, we have a General User post in which we are taking a definitive review of the INDEX MATCH, a vastly superior alternative to VLOOKUP.  The topic was last covered in TOTW #117 - although we will cover the whole topic again from scratch here.

How does INDEX MATCH work, and why is it better than VLOOKUP?

INDEX MATCH is actually two functions, that when combined can replace VLOOKUP, and also do much much more.  They are essentially opposites of one another:

MATCH finds a given value within a range, and returns an index number based on its position
INDEX takes an index number, and returns the value in a range at that position

Let's take this example:

Let's say we have been given the order number 100373, and we need a formula that will find the customer (given name) that made that order.  As an aside, VLOOKUP couldn't do this, as it only searches the first column of a table for a matching value.  But we can do this with INDEX MATCH:

We write a MATCH that searches column D within the table for 100373, which will output 7 as that entry is in the seventh position
We write an INDEX that takes the result of that MATCH, 7, and looks down column B of the table to return the seventh item, which is "Luis"; this is our solution.

How to write each function

INDEX looks like this:

=INDEX(range, position)

Range is a row or column that we will search for our desired output.
Position is the place in that row or column (counting from left to right or top to bottom) that the INDEX will go to.  This is usually calculated using a MATCH.

MATCH then goes like so:

=MATCH(lookup value, lookup array, match type)

Lookup value is the entry that will be looked for.  This must be a unique label for the vanilla version of MATCH.
Lookup array is the single row or column that will be searched.  Note that VLOOKUP and HLOOKUP are two separate functions, but INDEX MATCH can do searching in either direction with only one.
Match type is an option setting.  0 will be used if an exact match from an unsorted list is required - which is the majority of the time.  Taking approximate matches from sorted lists we will look at later.

The total function then:

=INDEX(output range, MATCH(lookup value, search range, 0))

There's an attachment at the bottom of this page which will show you this in practice.

Extending INDEX MATCH

There are several extensions and quirks that one can add to the basic INDEX MATCH function.

Accounting for repeated labels

Like VLOOKUP and other lookup functions, MATCH will only find the first matching cell in a range.  This means that it is not suited for circumstances where labels are duplicated.  If duplicates exist in one or more label fields, one workaround is to combine multiple fields into a single value using & until a unique lookup field is created:

This will overcome the fact that e.g. "Ruel" is duplicated in the 'Family name' field.  The inputs for our lookup function will then also be combinations of multiple fields.  There is an example in the attachment.

Inexact MATCH

The option setting (third input) of MATCH chooses between three modes:

0 - The lookup array may be ordered or unordered; only an exact match will be accepted.
1 - The lookup array must be in ascending order; the closest match to the search term without going over will be found.
-1 - The lookup array must be in descending order; the closest match to the search term without going under will be found.

The 1 and -1 options can be very helpful when e.g. sorting values into buckets, such as grades; or for finding the most recent / next date in an ordered list.

In this case, we want to MATCH each student's score against the 'From' column.  There is an example in the attachment.

2D indexing

There is another version of INDEX that can also be used by specifying an extra input:

=INDEX(2D range, row number, column number)

This version can be used with one or two MATCH functions to pull a value from the intersection of a given labelled row and labelled range.

The function will end up looking like this:

=INDEX(entire range, MATCH(row search value, row labels, 0), MATCH(column search value, column labels, 0))

Once again, there is an example in the attached file.

Indexing an entire row or column

Building from the previous case, we can also use a trick with a two-dimensional INDEX to get it to return the entire of a given row or column as an array.  This is useful when summarising e.g. with a SUM or AVERAGE.

Using a 'Row number' of 0 will return all the rows in a given column
Using a 'Column number' of 0 will return all the columns in a given row

And finally, there is another example of this in the attachment at the bottom of this post.

There are also some practice tasks for you to try in the attachment.

 

Previous post
TOTW index 
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

INDEX MATCH redux.xlsx
Anonymous