How To Guide: INDEX & MATCH Functions In Excel

Mar, 24, 2016


WHY USE INDEX AND MATCH OVER VLOOKUP?

Many agree that INDEX and MATCH are better formulas to use over VLOOKUP because they offer much more flexibility. With VLOOKUP when you add a column or a row to the spreadsheet you have to manually change the formula to obtain an accurate result. In comparison when using INDEX and MATCH formulas you can add rows or columns and it will not cause any errors: the result of the lookup will still be correct. By using INDEX and MATCH formulas you can reduce the number of errors in a spreadsheet, work much faster on large spreadsheets and be able to create more complex Excel models.

What are index and match functions?

INDEX and MATCH are two distinct Excel functions yet combining them together in a single formula can give the user a powerful information lookup tool. INDEX function simply returns the value of a specific cell in an array of data. The position of the cell in the array is to be defined by the user, by indicating its relative row and column coordinates.

The syntax of INDEX is the following: = index(array, row number,[column number])


for EXAMPLE…

This formula: =index($B$3:$E$7,3,3) would return ‘Paris’, as it is located on the 3rd row and 3rd column of selected array.

INDEX & MATCH Functions in Excel VLOOKUP UPSLIDE

Now let us introduce the MATCH function. This function allows the user to look for a certain value in a given array and returns the corresponding cell relative position. The syntax is as follow: =match(lookup value, array, match type)

for EXAMPLE…

If we apply a MATCH function on the table like that: =match(”Jessica”,$B$3:$B$7,0) we would get a value of ‘3(“Jessica” value is located on the 3rd row of the selection)

INDEX & MATCH Functions in Excel VLOOKUP UPSLIDE

IMPORTANT NOTES

– The array must be unidimensional.
– The match type value should be input as ‘0’ for an exact match.


The combination of INDEX and MATCH allows the user to obtain a value corresponding to an input parameter, the parameter and value to return being in different data table’s columns and same row


To do this, the syntax is the following: =index(array containing value to return, match(parameter, array containing parameter,0)).

for EXAMPLE…

For example, if we want to get the city corresponding to the name Maria in the table, we should write the following formula: =index($D$3:$D$7,match(“Maria”, $B$3:$B$7,0)) and we would get ‘Rome’ as a result.

INDEX & MATCH Functions in Excel VLOOKUP UPSLIDE
Once well understood and mastered, this formula can be generalized (to return a value located in the same column as the parameter but in a different row for example) and used for other lookup schemes.

Hope this helped! any questions?
FEEL FREE TO COMMENT BELOW OR TWEET US!


Author: Chandni Trehan