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
INDEX and MATCH
- INDEX accepts a row number and column number and returns a value at this location
- If the array is a single row or column, INDEX only accepts one argument
- MATCH returns the position of lookup value in a single row or column
- INDEX and MATCH combine to form faster and more flexible lookups than VLOOKUP
INDEX(array, row_num, [column_num])
--- array: Usually the full dataset, including the headers
--- row_num: The row number you wish to lookup
--- column_num: The column number you wish to lookup
MATCH(lookup_value, lookup_array, [match_type])
--- lookup_value: The value you want to search for
--- lookup_array: The group of cells to be searched
--- [match_type]: 0 finds first value equal to lookup value. Almost always use this
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
F4: Anchor cells (when inside a formula)
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 =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 2 and column 3.
I'll then close the bracket and press Enter.
And this returns the Grove Boulevard address for my first client.
Now as you�ve probably noticed, INDEX by itself is pretty useless.
However, combined with MATCH it can perform very flexible lookup functions.
Match searches along a row or a 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 =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 0 for exact match.
Then we'll press Enter to finish.
And this tells me that Blammo Corp is in position 7 in the company name column.
Next I need to find the position of the sales person in the sales header array.
So I'll write =match again, the lookup value will be sales person, the array will be sales header, and the match type will be 0.
I'll press Enter again and we can see that the sales person position is 8.
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, and 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 =index and in the array I'll select sales transactions, and then in the row number I'll write match, the lookup value will simply be Klimpys, the lookup array will be the company names, and the match type will be 0.
I'll then press a comma and move onto the column number.
Again this will be a match function.
The lookup value will be sales person, 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 0 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 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.
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 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.