Excel Index Match Vs Indirect
Originally published: 12/06/2020 06:46
Publication number: ELQ-28005-1
View all versions & Certificate
certified

Excel Index Match Vs Indirect

This is to demonstrate the difference between INDEX MATCH vs INDIRECT

Description
This is to demonstrate the difference between INDEX MATCH vs INDIRECT.
Specifically aimed at intermediate and advanced excel users who use or would like to learn more about INDEX, MATCH and INDIRECT functions.
INDEX MATCH are useful combination of functions to search and call out in an excel table data of a particular cell. There is usually a practical and speed related compromise in this process and hence using INDIRECT could be a quick fix. However using INDIRECT comes with its shortcomings vs the INDEX MATCH MATCH option.
The sheet example takes a simple table with date and country in the 2 axes with population data in the cells. Indirect needs to reference text cell numbers while INDEX MATCH can call on the exact position of the cell that is being called on. In this case specifying the country and the year will provide the exact location of the cell with the corresponding data and the output that information in the formula.
Using this example one sees the advantage and can then build on it to use in complex business plans, excel models and other situations.
When possible and when time allows, recommend to use INDEX MATCH over INDIRECT function

This Best Practice includes
1 Excel sheet

Aram K offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss


0.0 / 5 (0 votes)

please wait...