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.
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
The INDEX function returns a value from an array (search field) based on the row and column numbers you specify:
In the below example the INDEX formula is returning the score for Helen
=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.
The MATCH formula searches for a lookup value in a range of cells, and returns the relative positon of that value in the range.
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
=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
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
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:
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.
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.
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.
Please leave your thoughts and comments below, especially if there are any areas of Excel you would like an article about.