You’ve probably come across the MIN and MAX functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know who or what got the lowest/highest figure?
Let’s take a simple example;
So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.
By using the MIN and MAX functions I can quickly find the lowest and highest values using MIN(B2:B11) or MAX(B2:B11), but it doesn’t tell me “who” spent these values.
So how can you get around this?
This is where INDEX and MATCH come in. If you are unfamiliar with these two functions, check my blog https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with INDEX and MATCH.
To get the name of the person with the lowest/highest spend I need the following formula;
The MATCH part of the formula find finds the lowest value in cells B2:B11. It then uses this number and finds its position within the range. In this example the MATCH function will return the value 4 i.e. it is the 4th value in the selected column of data. The index function then looks within range A2:A11 and looks in the 4th row – the value returned by MATCH. You don’t need to reference the column because there is only one column selected in the array portion of the INDEX function.
The find the person with the highest spend, the formula would be;
So as you can see INDEX and MATCH, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.
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.