How to Apply INDEX and MATCH Separately and Combined | Advanced Excel

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.”

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.