VLOOKUP Vs. INDEX-MATCHDifferences, Similarities, and How to Use Them

The VLOOKUP function is probably one of the most well-known and used functions in Excel.

With the utility that VLOOKUP provides, it’s really not unexpected. It’s also relatively easy to use too.

This combination of utility plus ease of use is probably the reason why it’s used by many Excel users.

The thing is, there’s another function (well, more of a combination of functions) that does the same thing as VLOOKUP (and some more).

The combination of the INDEX and MATCH functions can do the same thing as VLOOKUP but without its limitations.

So the question is, why do more people know of VLOOKUP than INDEX-MATCH?

In this article, we’ll be trying to find the answer to this question. Which of the two is better? Let’s find out.

Similarities Between VLOOKUP and INDEX-MATCH

Before we delve into the differences between these VLOOKUP and INDEX-MATCH, let’s first discuss the similarities between them:

• Both do the job of a lookup Either of the two looks up a value from the array and returns a value that matches it
• Availability – both VLOOKUP and INDEX-MATCH are available in all versions of Excel
• When it comes to referencing data in other worksheets or workbooks, both can do so

Now that we have that out of the way, let’s proceed to compare the two.

Ease of Use: VLOOKUP vs INDEX-MATCH

Of the two, VLOOKUP is relatively the easier one to use.

For one, VLOOKUP is a stand-alone function and not a combination, unlike INDEX-MATCH.

Just with that, you can already see why VLOOKUP is easier to use. You only need to mind the arguments for one function.

On the other hand, you need to mind the arguments of two functions with the INDEX-MATCH combo.

VLOOKUP only requires four arguments, one being optional.

So technically, it really only needs three arguments for it to work properly.

In contrast, the INDEX-MATCH combo requires six arguments at least, three for the INDEX function, and another three for the MATCH function.

Then there’s the matter of having the combine these two functions.

If I were to teach a beginner a lookup function, I would probably teach him/her VLOOKUP first before teaching the INDEX-MATCH combo.

It’s easier to use and remember.

Just take a look at this illustration.

Which formula is easier to remember?

Flexibility: VLOOKUP vs INDEX-MATCH

If I’m asked why I would prefer to use the INDEX-MATCH combo rather than VLOOKUP and I can only give one reason, I would answer flexibility.

The INDEX-MATCH combo is objectively more flexible than VLOOKUP.

While yes, VLOOKUP is a great and powerful function, it still has its limitations.

One, it won’t work properly if the column the lookup value belongs to is not to the left of the results column.

Most of the time, you use VLOOKUP when the lookup value is located in the leftmost column of the array.

Another limitation is that it can only work with data that is arranged vertically (hence, Vertical LOOKUP).

Lastly, it makes a static reference to the column. If you add or delete a new column in your array, VLOOKUP will return a wrong result value.

You can address these limitations such as rearranging the columns, changing the orientation array, or making a dynamic column reference.

But at that point, why not just use INDEX-MATCH? The main draw that VLOOKUP has over the INDEX-MATCH is its ease of use.

If you do more things just to address the limitations of VLOOKUP, then I don’t see a compelling reason to use it over INDEX-MATCH.

The INDEX-MATCH combo does not have any of the above limitations of VLOOKUP.

The lookup column and results column could be arranged in any way and INDEX-MATCH will still work properly.

It can also data whatever it’s orientation (vertical or horizontal).

Lastly, it makes a dynamic reference to the lookup and results rows/columns.

Even if you add or delete rows/columns, INDEX-match will still give the correct result (unless you deleted either of the lookup or results rows/columns).

Example

Just to show much more flexible INDEX-MATCH is than VLOOKUP, let’s rearrange the columns of our previous illustration:

As you can see, VLOOKUP returned an #N/A error value but INDEX-MATCH still works properly.

Speed: VLOOKUP vs INDEX-MATCH

When I say speed, I mean the processing speed of the function (or in the case of INDEX-MATCH, a combination of functions).

While the difference in speeds is barely noticeable when working with a small dataset, it’s not the case when working with larger ones. I’m saying as large as having thousands of rows and hundreds of columns.

In this regard, the INDEX-MATCH is noticeably faster than VLOOKUP. When looking up a large array, VLOOKUP tends to lag a bit.

That said, it’s not like you’ll always be working on large datasets so speed won’t always matter.

Also, Microsoft is constantly making improvements to the speed of various functions such as VLOOKUP. In the future, there might come a time when there is no speed difference between VLOOKUP and INDEX-MATCH.

VLOOKUP vs INDEX-MATCH: Which is Better?

So which of the two is better? I’d say it depends (though I generally prefer INDEX-MATCH over VLOOKUP).

Of the two, VLOOKUP is the more newbie-friendly option. It’s easy to learn and remember. And if the dataset does not touch on its limitations, It does its job just fine.

On the other hand, the INDEX-MATCH combo is the more flexible option.

It does not have the major limitations VLOOKUP has. It’s also relatively faster when working with large datasets.

What I suggest here is that if you’re still new to using these lookup functions, learn how to use VLOOKUP first.

Then, if you already know how lookup functions work, learn how to use INDEX-MATCh next. The flexibility of INDEX-MATCH just cannot be understated.

Alternatives to VLOOKUP and INDEX-MATCH

If you still want to know more lookup and reference functions, here are some of them:

• HLOOKUP – HLOOKUP (or horizontal lookup) is the horizontal counterpart of VLOOKUP. It works best if your dataset is arranged horizontally. It’s available in all versions of Excel
• XLOOKUP – the XLOOKUP function is an improvement of the VLOOKUP and HLOOKUP functions. It does what these functions do but without the limitations. Functionally, it’s the same as the INDEX-MATCH combo. XLOOKUP is currently only available in Excel 2021 and Excel 365.
• LOOKUP – the LOOKUP function looks for a value in a single row or column and returns a value from the same position in another row or column; unlike VLOOKUP, it can only do an approximate match