Mar, 24, 2016

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.

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])

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.

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)

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)

– 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, 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.

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.

- 50 Most Powerful M&A Firms in the World (9,515 visits)
- The 9 Most Influential Accounting Firms in the World (7,139 visits)
- 8 Ways To Make Beautiful Finance Charts in Excel (3,031 visits)

Perform tasks in Excel, PowerPoint and Word 5x faster

START YOUR FREE TRIAL NOW