Powerful Excel Formula: INDEX MATCH

In a previous article, we discussed the VLOOKUP formula and how one of its major flaws was that it will only search from left to right and adding new columns into your search array can break your formula. The INDEX MATCH formula will fix those problems and may persuade you to use VLOOKUP far less.

Download the accompanying file(s) for this article.

What does INDEX MATCH do? As the name suggests it combines two formulas to make one super formula, however, it is simple to understand.

It’s advisable, but not necessary, to read our earlier article about VLOOKUP before reading on

INDEX

The INDEX function returns a value from an array (search field) based on the row and column numbers you specify:

INDEX(array,row_num,[Column_num])

In the below example the INDEX formula is returning the score for Helen

Click on the image to expand it
Click on the image to expand it

 =INDEX(B3:C7,     This is the array

2                                              Row 2

2                                           Column 2

The returned value is 20

This is a simple INDEX formula but illustrates the point.

 

MATCH

The MATCH formula searches for a lookup value in a range of cells, and returns the relative positon of that value in the range.

MATCH(lookup_value,lookup_array,[match_type])

The match type can be either 1,0 or -1:

 

1: Finds the largest value that is less than or equal to the lookup value. To use this option the values in the lookup array must be in ascending order

0: Exact match. I use this option most frequently

-1: Finds the smallest value that greater than or equal to the lookup value. To use this option the values in the lookup array must be in descending order

 

In the below example the MATCH formula is returning the relative position for Helen

Click on the image to expand it
Click on the image to expand it

=MATCH(E3,                     This tells Excel that you want to find the position of the value in cell E3

B3:B7,                              This is the lookup array

 0       

 

The value returned is 2, because Helen appears in row 2 of the search array.

Why do you care what position a value is in an array? Because that position is the ROW number which is very useful in an INDEX formula and works wonders with an INDEX MATCH formula

 

INDEX MATCH

Now that you can use INDEX and MATCH on their own, how can we combine these to make a super formula? The formula is written:

=INDEX(column to return a value from, MATCH(lookup value, column to lookup against, 0))

In the following example, we are going to find the number of goals a player has scored and for which club he plays. Our data is arranged in a table, all we need to do is create the formula to find the team:

=INDEX(C3:C22,MATCH(G2,D3:D22,0))

Click on the image to expand it
Click on the image to expand it

Let’s break that down:

 =INDEX(C3:C22             We are indexing this array for the teams

 ,MATCH(G2                        We are wanting to match the value (team) in cell G2

 D3:D22,                              This is the array for the list of players

 0                                             We want an exact match

 

The value returned for ‘Costa’ is Chelsea, which is the correct value.

To return the value for how many goals Costa has scored the INDEX formula needs changing to Column B, as this is where the goals data is held.

Because this formula does not require you to specify the column number to find the answer, as VLOOKUP does, you can insert new columns and the formula will remain intact.

 

Preventing Errors

What happens if you try to search for a player who isn’t in the list? Excel returns horrible #N/A results. A good way to prevent this is to start the formula with IFERROR and enter “Player Not Found” as your error statement.

Click on the image to expand it
Click on the image to expand it

Another solution would be to use a data validation box for the player names, thereby preventing users from entering an incorrect value.

 

Can we make the formula easier to read?

Of course we can! Assume that you aren’t the person using the file, would you be able to quickly understanding what the formula is doing? The solution is to use named ranges. The benefits here are two fold 1) your formula is much easier to write and 2) your formula is much easier to read.

Click on the image to expand it
Click on the image to expand it

Please leave your thoughts and comments below, especially if there are any areas of Excel you would like an article about.

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Powerful Formula

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *