Excel Tips - Index Function

 
 
 

Index Function in Excel

The INDEX function in Excel is a powerful lookup function that returns a value at any determined position in an array. The function can return individual values or entire rows and columns. The INDEX function is typically used in combination with the MATCH function, where the MATCH function sends a position to the INDEX function.

The INDEX function syntax is as follows:

=INDEX (array, row_num, [col_num], [area_num])

array – a range of cells
row_num – the row position in the reference or array
col_num – [this argument is optional] the column position in the reference or array
area_num – [this argument is optional] the range in a reference that will be used

Index Match Function

The INDEX function combined with the MATCH function is an even more effective tool. This allows the formula to be dynamic rather than static. The MATCH function imbedded inside of the INDEX function allows users to perform advanced lookups. The MATCH function is used to return any position in an array, whether the array is vertical or horizontal. The MATCH function imbedded inside of the INDEX function can allow flexibility to return positions for either the column or row.

The MATCH function syntax is as follows:

=MATCH (lookup_value, lookup_array, [match_type])

lookup_value – the value that will be matched in the array
lookup_array – the range of cells or an array
match_type – [this argument is optional] 1 is exact or next smallest, 0 is an exact match, -1 is exact or next to largest

Although match type is optional, it is very useful in finding accurate results. If one decides to omit the match type, the automatic default is 1.

  • 1 – In order to find the exact or next smallest value, the array must be sorted in ascending order.

  • 0 – The lookup_array does not need to be sorted in order to find an exact match.

  • -1 – In order to find the exact match or next largest value, the array must be sorted in descending order.

For example, if we type the formula =MATCH(E2,B3:B8,0) the function will return the word “Gatorade”, please see below:

Index Match Match Function

The INDEX MATCH MATCH function is the most dynamic of the lookup functions. For example, we can use the MATCH function to return the positions for both the column and row by embedding two MATCH functions inside of an INDEX function.

Index Match vs. Vlookup

Although the VLOOKUP function is a great tool, the INDEX MATCH function has all of the functionality of the VLOOKUP and more. The INDEX MATCH function is much more dynamic and allows more flexibility within its searches. The VLOOKUP should be used for simple configurations which require one function. The VLOOKUP function can only look to the right for values while the INDEX MATCH function can look to the left and right. Not only does the function look to the left and right, but it can also work within horizontal and vertical ranges while the VLOOKUP function can only use vertical ranges. The INDEX MATCH function is much faster when working with large data sets and is much more favorable.

 

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions on the INDEX function or if he can help you with your modeling needs.

 
Eric Bergin