Sign in or start a free trial to avail of this feature.
3. Better Lookups with INDEX/MATCH
Combine the INDEX and MATCH commands to create more flexible and faster lookups. Essential for budding Excel power users
Using INDEX (00:21)
The INDEX function finds the value at a specified position in a data array. It takes three arguments. First is the array to search. Second and third are a row number and a column number. INDEX returns the value at the given position.
Using MATCH (01:06)
The MATCH function identifies the position of a specified lookup value in a single row or column. It takes three arguments. First is the value we want to lookup. Second is the array where we want to search for the lookup value. Third is the match type. You’ll generally want this to be 0 for an exact match. MATCH returns the position of the lookup value in the row or column.
Combining INDEX and MATCH (02:10)
We combine INDEX and MATCH by writing an INDEX function where the row number and column number are both determined by MATCH functions. In this lesson, we want to find the sales person for a series of companies. For each company, we use MATCH to find the row number of the company. We use another MATCH to find the column number containing sales people. We then use INDEX with these row and column numbers to find the sales person for each company.
Advantages of INDEX and MATCH (03:55)
Combined INDEX and MATCH functions can look long and awkward compared to VLOOKUP, but there are several advantages to INDEX and MATCH. First, the lookup column does not have to be to the left of the output column, so you don’t need to reorganize your data. Second, INDEX and MATCH are much faster than VLOOKUP. This can make a big difference for larger data sets.
The INDEX and MATCH functions combine to form an alternative way of performing lookups. In some instances they can be more complicated to write, but they do offer much more flexibility than VLOOKUP and HLOOKUP. In this lesson, we're going to replicate the watch list task we performed in a previous lesson using INDEX and MATCH. Let's first look at the INDEX function. INDEX simply returns the value at any position within an array. So if I write the INDEX function equals index, I then select an array, which is going to be the sales transactions array. And then I select a row number and a column number, INDEX will return the value at that position.
So if I'd like the address of the first client, I'll select row two and column three. I'll then close the bracket and press Enter. And this returns to Grove Boulevard address for my first client.
Now as you've probably noticed, INDEX by itself, it's pretty useless. However combined with MATCH, it can perform very flexible lookup functions. MATCH searches along a row or column to find a lookup value and then returns its position. Let's start by finding the position of Blammo Corp in the left hand column. So I'll write equals match.
And the first value I must include is the lookup value.
So I'll select Blammo Corp.
And the next value I must select is the lookup array. And this is the company name.
And lastly, I must enter the match type. And I'll press zero for exact match.
Then we'll press Enter to finish. And this tells me that Blammo Corp is in position seven in the company name column.
Next I need to find the position of the salesperson in the sales header array. So I'll write equals match again.
The lookup value will be salesperson.
The array will be sales header.
And the macth type will be zero. I'll press Enter again and we can see that the salesperson position is eight.
By writing an INDEX function, first selecting the array then selecting the row number, which is what we calculated earlier, and then the column number then press Enter to finish.
And this gives us the correct answer in Parrish. Now let's move up to the watch list and perform these two steps in one equation. So I'll write equals index.
And in the array I'll select sales transactions and then the row number, I'll write macth.
The lookup value will simply be Klimpys.
The lookup array will be the company names.
And the match type will be zero.
And then press the comma and move on to the column number. Again this will be a MATCH function.
The lookup value will be salesperson.
And I'll anchor this with F4.
Next we'll go to the lookup array which will be sales headers.
And then we'll press comma and zero for exact match and then close both brackets and Enter.
And again this gives us the correct answer for Baines. We can now copy this formula for the remaining companies.
And we'll also copy it for Parrish.
And now all of our watch list is performed using the INDEX and MATCH function.
Given the length of this formula, you might be thinking that the VLOOKUPs are a better option than INDEX and MATCH. But INDEX and MATCH have a number of big advantages over VLOOKUP. Firstly, the lookup column does not need to be to the left of the output column. This means that there's no need to move around lookup columns before manipulating a data set. Secondly INDEX and MATCH is a much faster calculation than VLOOKUP. For small data sets, this isn't noticeable. But for larger data sets, you'll see a significant delay when using the VLOOKUPs in your spreadsheets. For these reasons, if you're serious about improving at Excel, be sure you know how to use INDEX and MATCH as well as VLOOKUP. As an exercise, try to calculate the payment date using the INDEX and MATCH function for all of the companies in the watch list. You might want to start off by completing the INDEX and MATCH functions in two separate steps as I did earlier before combining them into one equation. In the exercise, make sure you include the sales headers in all of your arrays. This is a very common mistake made when using INDEX and MATCH but thankfully one that's easy to avoid.