Some Excel Wizards might say INDEX MATCH is powerful because of its flexibility and dynamic nature. It addresses the limitation of VLOOKUP and may replace it completely.
On the other hand, most users still prefer VLOOKUP as it is the oldest, most common, and easiest function.
If you are confused in choosing between them, I will help you make an unbiased decision. Here, I will be comparing these two functions based on several factors and features in detail.
Excel’s VLOOKUP function searches the lookup value in the lookup table vertically and returns an item from the specified column.
|=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
|lookup_value: search item
table_array: cell range that contains your lookup value
col_index_num: a column that contains the return item
[range_lookup]: choose return match
TRUE/1: Nearest Match
FALSE/0: Exact Match
INDEX MATCH are two separate functions INDEX() and MATCH() combined together to perform more dynamic lookup.
|=INDEX(array, row_num, [column_number], [area_num])
|array: cell ranges
row_num: row index that has your return value.
[column_num]: enter the column index with the return item.
[area number]: cell range to return item.
|=MATCH(lookup_value, lookup_array, [match_type])
|lookup value: search value
lookup_array: table or range that has your search value
match_type: specify your return match
1: largest match
0: exact match
-1: smallest match
INDEX MATCH formula:
=INDEX(column_number, MATCH(lookup_value, lookup_array, [match_type]))
Comparing VLOOKUP vs INDEX MATCH
In terms of the function’s popularity and user experience, the VLOOKUP function is definitely on top compared to the INDEX MATCH function.
VLOOKUP’s function arguments are more straightforward and beginner-friendly. Also, since the function is widely used and recognized as an important Excel skill, you can find a lot of common people.
For Instance, you are more likely to find a colleague who uses VLOOKUP than the INDEX MATCH.
Talking about the INDEX MATCH, the users must first understand each function’s workings individually. Then, learn how to combine and best use the two functions together in Excel.
So, many users get intimidated by the nested two-formula arguments and mostly opt for the easier ones (VLOOKUP).
Honestly, Excel gurus who have already mastered the VLOOKUP function are the ones who discover the INDEX MATCH as an alternative.
While selecting the lookup and reference functions, we usually check whether the function searches for value horizontally/vertically or from right/left.
Well, the INDEX MATCH function beats the VLOOKUP in Lookup Direction.
VLOOKUP’s nature is to always search for the values to the right of the specified column and in a vertical array.
If you want the formula to search at its left, it isn’t the best option as you will receive a #N/A! Error.
In short, it’s limited to only the right and column lookup.
On the contrary, INDEX MATCH, also best known as a dual function, can search values in the horizontal and vertical array.
This is why once you learn this function you wouldn’t have to switch between HLOOKUP and VLOOKUP functions separately.
Another notable feature is that it can search for values from both right and left. Since the function overcomes VLOOKUP’s main limitation, Excel users prefer INDEX MATCH as an alternative.
Flexibility in Column Reference
In both VLOOKUP and INDEX MATCH functions, you know that the return output is based on the column you specify.
In VLOOKUPs, we enter the column number to retrieve the value from like 1,2,3, and so on. So, in this case, the column reference is static and triggers the result when you update the column.
For Instance, let’s consider a formula
=VLOOKUP(G4, B3:E12, 3, 0)
Here, B3:E12 is a table array and the column index is 3. If I add a column between C and D, the formula returns 0 because now the 3rd column is empty.
However, in INDEX MATCH, we are referencing a whole column range to get the data instead of a single one. Even when you insert columns, the formula still results in the same output as shown in the image.
Therefore, when you make changes to the column, the INDEX MATCH formula remains unaffected while the VLOOKUP formula becomes invalid.
This implies INDEX MATCH is more flexible and definitely the winner in terms of Column Reference.
Lookup Value Size
For VLOOKUPs, the character limit for the lookup value is 255. If you surpass it, Excel sends you #VALUE! Error.
In case of INDEX MATCH, there are no specific character limits for the search item. So, it’s crystal clear that INDEX MATCH takes over the VLOOKUP based on lookup value size.
However, if the length of your lookup value isn’t that long, it does not matter whichever function you use.
I’d have to say no to the VLOOKUP. This is because when you use the auto-fill handle horizontally, you’ll still get the same result.
On the other hand, for INDEX and match, the Auto Fill works absolutely fine as the formula moves and results in the expected output.
Speaking of the function’s speed and calculation time in smaller datasets, you won’t notice any variation in both functions. If VLOOKUP is easier for you, just go for it!
However, for huge datasets, INDEX MATCH is relatively faster than the VLOOKUP. It’s because Excel does not have to search the lookup value in the large table ranges.
Feasibility Across Sheets
Using the lookup functions, you may find the need to draw out the return items from other sheets to your current worksheet.
As Sheet Referencing itself is quite intimidating to many users, performing look-ups across multiple spreadsheets can be difficult at first.
I would have to agree that VLOOKUP between sheets is simpler and more comprehensible than using INDEX MATCH.
This is because for INDEX MATCH, you need to reference sheets in both INDEX and MATCH arguments. The formula could get long and confusing.
VLOOKUP vs INDEX MATCH: Final Verdict
To conclude, VLOOKUP is best to use when you simply want to retrieve data from a column in a small file size. It’s also easier to look up values across sheets.
However, if you want a more dynamic, flexible, faster lookup function for huge spreadsheets, INDEX MATCH is a top tier.
Again, here’s a quick tabular summary to help you decide the better function.
|Flexibility in Column Reference
|Lookup Value Size
|Feasibility Across Sheets
To conclude, it’s clear that INDEX MATCH is the winner here.
But, do you know that there’s an XLOOKUP function that outperforms all lookup functions?