Feel free to download our Exercise file and practice along with the video. And don't worry, there is also a Solution file with all the formulas.
We have more explainers on advanced Excel functions you might be interested in - VLOOKUP and MATCH; VLOOKUP COLUMN and ROW; INDIRECT, VLOOKUP and INDIRECT.
In this extract we will see two interesting Excel functions: INDEX and MATCH. We’ll show them within the same video because they are often applied together. Their combination offers an interesting alternative to VLOOKUP. Let’s see the application of the two functions separately and then combined.
INDEX
INDEX is a formula which returns the value located at a given intersection within an array.
So basically, the INDEX formula needs us to indicate the following:
1) An array where it will function
2) A row number
3) A column number
The formula will go and find the specified row and the specified column within the array and deliver its content.
For example if we select the array from B4 to C12 in our table, and choose 5 as the row argument and 2 as the column argument, we will obtain “Italy” as a result. The INDEX function simply delivers the cell which has the coordinates we chose. Within the fifth row and second column of the range we selected lies “Italy.” Right? We indicated those coordinates and INDEX provided us the result. Let’s do another try. I’ll select the same range – from B4 to C12. This time, let’s pick 1 as the row argument and again 1 as the column argument. The result is “Borussia,” given that the value in the first row and first column of the selected range is “Borussia.”
MATCH
The next function we will see in this video is MATCH. It returns the relative position of an item within an array.
Let's illustrate that with an example. I'll type the MATCH function. The first argument that needs to be selected is the lookup value – in our example, that will be "Milan", lying in B19. After that, we need to specify which is the array where the lookup value's position needs to be found. Let's select all teams that lie within the range from B4 to B12. The third argument is a logical value – “0” or “1,” standing for "an exact match" and "closest match." I'll select 0 as we need an exact match. Our formula is ready.
The output of the function is “2,” which represents Milan's position within the selected array.
It is important to notice that the formula can be applied also vertically. For example, if we go on and look at number of games played within the array from B3 to F3, the result will be four, which is correct as "Games Played" is the fourth column within the array.
INDEX and MATCH
Let’s consider the combination of INDEX and MATCH together. This is a pretty powerful tool, often considered superior to VLOOKUP as it allows users to have a flexible lookup value within the Source table. It is intuitive to combine INDEX and MATCH. The first formula needs as an input relative positions within a range, and the second formula provides that. Let's see how the two functions are applied together in practice.
We have a task similar to the one before. Two tables. The one on the left is our source table and the one on the right needs to be filled.
Exercise
Let's do the exercise in two steps.
First we'll apply the MATCH formula to each of the blank cells in the table on the right.
So we have +MATCH, the lookup value is Milan in cell I4. Let's fix its column reference as this will be the lookup value of our table. OK, let's select as a lookup array the cells from B4 to B12 and fix them. We are looking for an exact match and I'll type 0 as the third argument of the formula. Let's copy the formula on the row below as well. The results that we obtained are that Milan is in the second position and Hamburger is in the fourth position within the selected array, and this is correct.
Now, let's copy the two formulas for the rest of the blank cells. OK. So what we have here is the position of the lookup value within the source table. It does not do much work... but only for now.
Let's type INDEX in front of the MATCH function and add a left parenthesis. INDEX needs an array as a first argument. The array is the range of cells among which the output of the INDEX formula is selected. Let’s select the cells from C4 to C12 and fix their row references. Then we can close the parentheses. The result we obtained is “Italy.” Let’s recap how the two functions work together.
The INDEX formula needs as an input the number of the row within a range. Right? And we had found the number we were looking for, thanks to the MATCH function. In other words, the MATCH function indicates the position of the result and the INDEX function extracts it.
Remember, the INDEX function's first argument is the array of the source from which we want to extract the result. The second argument of the INDEX function is the MATCH function, which finds the exact piece of data that we are looking for.
We can copy the formula downwards and to the right because its arguments are fixed properly.
When to use INDEX and MATCH instead of VLOOKUP
Now, I'd like to show you an example of when INDEX, MATCH is superior to VLOOKUP. Let's cut the values under the column “Team” within the source table and paste them in Column G. Nothing changed with the table on the right. This is because the combination of INDEX and MATCH works, even when the lookup value is on the right. Had we used VLOOKUP and done this we would have seen an instant N/A error message. The combination of INDEX and MATCH is great for situations when you have an original source sheet and you don't want to change anything in it and your lookup value is on the right.
We hope this explainer was useful to you. Next, you can watch our video explainer about INDIRECT Excel Function: how to apply it and combine it with VLOOKUP and take our Introduction to Excel course to enhance your skills.
