Why INDEX-MATCH Is Powerful than VLOOKUP
Updated: Oct 25, 2021
When you want to look up data in various ways, you should never use a worksheet function with "lookup" in its name. Here's the reason....
Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a “lookup” function would be a reasonable choice to make. However, it has limitations.
INDEX/MATCH is better in a number of ways and I’d argue it’s easier to use, although maybe not at first purely as it’s less familiar.
INDEX/MATCH has the following benefits:
You can do lookups where the returned value can be from a column to the left of the looked up value. This isn’t possible with a VLOOKUP.
You can significant speed up your calculation speed if you are returning multiple values from a single lookup as you can separate a single MATCH from multiple INDEXes. So much faster!
The column number is not hard coded in the formula so the INDEX/MATCH formula auto adjusts and doesn’t break when you insert or delete columns.
You can return data from a table, doing a 2D lookup, matching both a row header and column header.
Match could be sorted ascending/descending or for exact match (VLOOKUP true is only unidirectional)
INDEX/MATCH refer only to required columns, and hence are recalculated only when those columns change (VLOOKUP is recalculated when any value within the lookup range changes)
It is easier to do multi-column matches using INDEX/MATCH than with VLOOKUP