Excel – Using INDEX & MATCH (Part 1)
If you are familiar with the VLOOKUP and HLOOKUP functions then you will know how useful they can be. There is however one drawback in that they only search for the look up value in the first column or row of a selected look up table. In the majority of cases this is fine, but there may be times when you need to look both to the right and left of the look up column.
This is where INDEX and MATCH functions come in. On their own, they are probably two of the most useless functions going – together they are extremely powerful and sometimes jokily referred to as VLOOKUP on steroids! But to understand how they work we need to take at look at each one in turn.
The basic syntax for MATCH is:
=MATCH(lookup_value,lookup_array, [match type])
Which in plain English is…
=MATCH(what value are you looking for, where are you trying to find it, do you want a near or exact match)
Whereas V/HLOOKUP require a TRUE or FALSE (1 of 0) value in the final argument, MATCH has three options;
1 = nearest match under/less than the look up value
0 = exact match
-1 = nearest match over/greater than the look up value
MATCH basically returns the position (represented by a number) of the look up value in a single row or column.
If I am looking for “X” in a row of cells then my formula would be =MATCH( “X”,A1:F1,0)
My function would return the number 4 i.e. it is in the 4th cell, reading from left to right in my selected cells. If looking in a single column of cells then it returns the position reading from top to bottom. Not the most useful bit of information you are ever likely to extract from a spreadsheet if the truth be told!
There are two versions of INDEX, but the one I am looking at here is the basic one that only looks at one table. The syntax for INDEX is:
…or in plain English;
=INDEX(range to check, which row do you want to look in, which column do you want to look in)
Using INDEX on its own is not the most useful thing you can do in Excel. For example;
=INDEX(A1:D7,5,3) would return 32
I could have just as easily written =C5. So it looks like a very long winded way of referencing cells. However…when you combine MATCH and INDEX together you get a completely different beast.
I’ll use a table we use in class as it is good way of showing how it can be used.
As you can see, we have a number of towns/cities around the UK with distances between them. Rather than search myself for distances between two given locations, I want to be able to use a series of drop down lists to select locations and a formula to find the correct distance between them. So I need something dynamic to do this, and this is where INDEX and MATCH come into play.
I’m going to use a couple of drop down lists to make my choices easier rather than type out the names each time. I’ll assume you know how to create VALIDATION lists, if not please see my blog http://wp.me/p2EAVc-5b on how to set those up.
To break this down I will use the MATCH function on its own to return the row and column numbers for each town/city I choose from my drop down lists. First, calculate the row position;
In this example, I have chosen Southampton and the MATCH function returns the number 5.
Now for the column position;
This time I’ve selected Nottingham and therefore returns the number 10.
Change the location in each of the drop down lists and see the values from each MATCH function change. So now you can see that MATCH is a dynamic function when linked to something like a VALIDATION list.
As we saw before, INDEX relies on row and column numbers to work. Normally, these would be manually entered and therefore fixed. As MATCH can be dynamic, it returns a number we can use to our advantage by embedding a MATCH function in the row and column arguments of INDEX.
Our new nested function will look like this;
Although it looks complicated, and may take a while to get used to the nested syntax, it is basically an INDEX function with two MATCH functions sitting inside it.
This now gives you a totally dynamic search function. Although in this example the look up values are the top row and first column, INDEX & MATCH will work using the look up on any column or row in your table.
What’s really neat, is that if you decide to move the town names around a bit (sticking to the same column or row mind you!) it will still work because the MATCH function will look for an exact match of the text value chosen in the drop down. Unless the spelling changes, it will find it wherever it is in the row or column!
In Part 2 I’ll show you how you can use INDEX and MATCH across multiple tables as well as extracting information from one column based on data found in another.