INDEX and MATCH are two of the most versatile functions within Excel.
We will look at these functions individually to begin with but the real value comes from combining the two of them. They can serve as a useful alternative to VLOOKUP because they can look up in either direction – VLOOKUP is restricted to looking up left to right. Also if you have a large file looking up thousands of cells it can also be useful to use INDEX/MATCH rather than VLOOKUP because the calculations are faster.
The INDEX function brings back the value within a cell, based on the specified row and column references.
This example shows how you first select your data range (A2 to C29) then which row and column from that range you want to return. The 7th row is Pall Mall and the third column is it’s colour – Pink.
You can also refer to a cell to get your row or column values. In this case referring to F2 for the column number 2, which returns Pall Mall’s price of 140.
The MATCH function searches for a chosen value within a range of cells, returning the position of it in the range.
In this example the formula is looking for a match of the text “Pall Mall” within the range A2 to A29 and the result shows the 7th value in that range contains this text.
If there are multiple cells which match the value, the formula will just return the first instance as in this example bringing back the first row in the range that contains “Light blue”. The zero after the final comma denotes that we need a exact match here.
Using INDEX and MATCH together
Now we’re going to look at some of the circumstances where using these two functions together may be preferable to using a standard VLOOKUP formula. As mentioned earlier, VLOOKUP is limited to only searching from left to right and INDEX and MATCH are also faster functions to calculate than VLOOKUP.
Here the two functions are being used to lookup the value in F2 (Whitechapel Road) and return the price. In this example, there is no real benefit over using =VLOOKUP(F2,A2:C29,2,0) and they both achieve the same result.
However in this example, INDEX/MATCH has the added flexibility of looking up right to left and performing a “backwards VLOOKUP”. In this case looking for the first instance of “Orange” in the Colour column and returning the Property.
INDEX and MATCH can also combine to help us pick out a value from a table or matrix to perform a two-way lookup. Here we are matching the value in J1 (Euston Road) with our listing in column A and our month in J2 with our month name headers to find that Euston Road was purchased 7 times in April. The result of the formula will change depending on the highlighted values in J1 and J2.