Difference Between VLOOKUP vs INDEX MATCH
VLOOKUP in Excel is a function that is very useful in looking up values concerning the references. It can also be considered the most powerful formula present under Microsoft Excel to look up the values. However, what if I tell you there is another function that can be used to look up the values in Excel? Yes, you heard it true! There is another function in Excel, which can be used in combination to do the task the same as VLOOKUP. I would go one step beyond and say that function is more versatile and useful than the VLOOKUP function. That function is a combination of the INDEX and MATCH function. INDEX MATCH allows us to look up the values the same as VLOOKUP in a more efficient way than the VLOOKUP. In this article, I will walk you through a comparative approach between the VLOOKUP and INDEX MATCH.
Head to Head Comparison between VLOOKUP and INDEX MATCH (Infographics)
Below are the top 8 comparisons between VLOOKUP vs INDEX MATCH:
Key Differences between VLOOKUP and INDEX MATCH Formula
Let us discuss some of the major key differences between VLOOKUP vs INDEX MATCH.
1. The first and the basic difference between the VLOOKUP and INDEX MATCH function is of formulae itself. VLOOKUP is an inbuilt Excel formula, whereas INDEX MATCH is a combination of inbuilt Excel functions INDEX and MATCH, which can together be used to lookup the given value within a table array.
2. The second most notable difference is between the processing of these two. VLOOKUP uses the static data references to look up the values in an array which means the column references are denoted by numbers such as 1, 2, 3,… etc. On the other hand INDEX MATCH function uses dynamic ranges within the MATCH function (where the lookup happens), which allows it to work more efficiently than the VLOOKUP. See the below two images for your references.
3. Inserting or deleting a column in the table array under the VLOOKUP function provides misleading results as it uses the static data references. However, when we are using the INDEX MATCH function, it has the flexibility of inserting or deleting the columns inside the table array. It does not alter the result as we are using dynamic ranges within the formula. You can see the output of two of the functions after we inserted a new column named Location in the table array.
While using VLOOKUP, we are failing to capture the accurate salary value for the lookup value. Whereas, in INDEX MATCH, though we have added the column, the function was flexible enough to capture the right salary value concerning the lookup value.
4. VLOOKUP can only be used when you have the data from left to right, or in other words, it can only be used when the column associated with lookup value the first one in the table array. INDEX MATCH does not have such restrictions; it can be used to lookup the values from left to the right as well as from right to left. It makes this function more flexible compared to VLOOKUP.
5. VLOOKUP formula can only look up the values across vertical lines, i.e. columns. V at the start of the formula stands for vertical. Since it can only be used for vertical lookups, it automatically becomes restricted not to be used over horizontal lines (for this, we use HLOOKUP). INDEX MATCH, on the other hand, is flexible to move through columns as well as rows. Therefore, it can lookup values through both. In short, INDEX MATCH does the work of both VLOOKUP and HLOOKUP in a single formula.
6. VLOOKUP has size restrictions for lookup value which can’t exceed 255 characters. If any lookup value is exceeding 255 characters, the VLOOKUP function will produce an error. On the other side of the coin, the INDEX MATCH does not have any restrictions for the lookup value.
7. VLOOKUP is very poor when we try to drag the formula across rows or columns with the same reference value. It provides the same result as the first lookup result because we are using static references. On the other hand, INDEX MATCH is smart enough to find out the actual value associated with the column when we drag the formula across rows/columns. See the screenshot below for better visualization:
8. This is the only fact that goes in the way of VLOOKUP. It is easy to understand as well as implement in comparison with INDEX MATCH due to the simple nature it has. On the other hand, some users find INDEX MATCH as difficult to understand due to its combination of two formulae that seem complex.
9. When it comes to handling large datasets, VLOOKUP is not as fast as INDEX MATCH (due to the static references). It is said that usually, INDEX MATCH is 30% faster than the VLOOKUP on normal days.
Comparison Table of VLOOKUP vs INDEX MATCH
The table below summarizes the comparisons between VLOOKUP vs INDEX MATCH:
| VLOOKUP | INDEX MATCH |
| VLOOKUP uses the static data reference while looking up the values. | INDEX MATCH uses the dynamic data ranges while looking up the values. |
| Inserting or Deleting a column affects the VLOOKUP result. | Inserting or deleting a column does not affect the INDEX MATCH result. |
| VLOOKUP can only be used for looking up values from Left to Right. | INDEX MATCH can look up the values from Left to Right as well as Right to Left. |
| VLOOKUP only can lookup through vertical lines, i.e. columns and not through rows. | INDEX MATCH can lookup values through rows as well as columns. |
| VLOOKUP has a limit for lookup_value size. It should not be exceeding 255 characters. | INDEX MATCH does not possess any limit of size or length for matching values. |
| VLOOKUP is not friendly when it comes to copy and drag. | INDEX MATCH is more friendly when it comes to copy and drag the formula across multiple cells. |
| VLOOKUP is easy to understand as well as remember in comparison with INDEX MATCH. | INDEX MATCH is hard to understand as well as remember due to its complex nature that combines two functions/formulae. |
| VLOOKUP is not as fast as the INDEX MATCH when we are dealing with large data sets. | INDEX MATCH is usually faster than the VLOOKUP. |
Conclusion
After considering all these points, we can conclude that INDEX MATCH can be used as a flexible, versatile, fast alternative to the VLOOKUP function. However, it still might be the toughest one to tackle for some users compared to VLOOKUP due to the complex nature and combination Index Match poses.
Recommended Articles
This is a guide to the top differences between VLOOKUP vs INDEX MATCH. Here we also discuss the VLOOKUP vs INDEX MATCH key differences with infographics and comparison table. You may also have a look at the following articles to learn more –
