Excel - VLOOKUP vs. INDEX/MATCH - Which is better?

Question

I understand how to use each method: VLOOKUP (or HLOOKUP) vs. INDEX/MATCH.

I'm looking for differences between them not in terms of personal preference, but primarily in the following areas:

1. Is there something that one method can do that the other cannot?

2. Which one is more efficient in general (or does it depend on the situation)?

3. Any other advantages/disadvantages to using one method vs. the other

NOTE: I am answering my own question here but looking to see if anyone else has other insights I hadn't thought of.

Prescott

Just a note - this question is quite broad for SO, and is a lot of opinion (but yes, there are quantifiable reasons to use one over the other I'm sure). You can find many pages just by searching online that compare these two in great detail. – Fanchette

@Fanchette Good point. I had seen many of these comparisons before excluding the fixed column index number which isn't discussed much, so I figured I would share. – Prescott

Answer 1

I prefer to use INDEX/MATCH in practically every situation because it is far more flexible and has the potential to be much more efficient depending on how large the lookup table is.

The only time when I can really justify using VLOOKUP is for very straight-forward tables where the column index number is dynamic, although even in this case, INDEX/MATCH is equally viable.

I'll give a few specific examples below to demonstrate the detailed differences between the two methods.

---

INDEX/MATCH can lookup to the left (or anywhere else you want)

This is probably the most obvious advantages to INDEX/MATCH as well as one of the biggest downfalls of VLOOKUP. VLOOKUP can only lookup to the right, INDEX/MATCH can lookup from any range, including different sheets if necessary.

The example below cannot be accomplished with VLOOKUP.

---

INDEX/MATCH has the potential to use smaller cell ranges (thus increasing efficiency)

Consider the example below. It can be accomplished with either method.

Both of these formulas work fine. However, since the VLOOKUP formula contains a larger range than the INDEX/MATCH formula, it is unnecessarily volatile.

If any cell in the range B1:G4 changes, the VLOOKUP formula must recalculate (because B1:G4 is within the range A1:H4) even though changing any cell in B1:G4 will not affect the outcome of the formula. This is not an issue for INDEX/MATCH because its formula does not contain the range B1:G4.

---

Using VLOOKUP with fixed col_index_number is dangerous

The main issue I see with having a fixed column index number is that it will not update as it should if full columns are inserted. Consider the following example:

This formula works fine unless a column is inserted within the lookup table. In that case, the formula will lookup the value to the left of where it should. See below, result after a column has been inserted.

This can actually be alleviated by using the following VLOOKUP formula instead:

= VLOOKUP("s",A1:H4,COLUMN(H1)-COLUMN(A1)+1,FALSE)

Now H1 will automatically update to I1 if a column is inserted, thus preserving the reference to the same column. However, this is entirely unnecessary because INDEX/MATCH can accomplish this without this problem with the formula below.

= INDEX(H1:H4,MATCH("s",A1:A4,0))

I realize this is an unlikely scenario, but it always bothered me that VLOOKUP by default looks up based on a fixed column index that does not automatically update if columns are inserted. To me, it just seems to make the VLOOKUP function more fragile.

---

INDEX/MATCH can handle variable column indexes just as well, but longer formula

If the column index number itself is dynamic, this is really the only case when I think VLOOKUP simplifies things a bit, but again the INDEX/MATCH alternative is just as good, just slightly more confusing. See below examples.

---

INDEX/MATCH is more efficient for multiple lookups

(thanks to @jeffreyweir)

If multiple lookup values are needed for a single match value, it is much more efficient to have a helper cell with the match value. This way, the match only has to be computed once, instead of one for each lookup formula. See example below.

This match value can then be used to return the appropriate lookup values. See example below, (formula has been dragged to the right).

This manual "splitting" of the match value and index values is not an option with VLOOKUP since the match value is an "internal" variable in VLOOKUP and cannot be accessed.

---

INDEX/MATCH can look up a range, allowing another operation

Let's say for example you want to find a max value in a column based on the column name.

You can first use MATCH to find the appropriate column, then INDEX to return the range of that entire column, then use MAX to find the max of that range.

See example below, the formula in H4 looks up the max value of the column name specified in cell G4. This cannot be accomplished using VLOOKUP alone.

---

MATCH doesn't have to match an exact value

Usually MATCH is used with the third argument as 0, meaning "find an exact match". But depending on the situation, using -1 or 1 as the third argument of MATCH can be very useful.

For example, the following formula returns the row number of the last row in column A that contains a number:

= MATCH(-1E+300,A:A,-1)

This is because this formula starts from the bottom of the A column and works its way toward the top, and returns the first row number in the A column where the value is greater than or equal to -1E+300 (which is basically any number).

Then INDEX can be used in combination with this to return the value in that cell. See example below.

---

In Summary

VLOOKUP is, at best, as good as INDEX/MATCH and admittedly slightly less confusing in some situations. And at worst, VLOOKUP is much more unsafe and volatile than INDEX/MATCH.

Also worth noting that if you want to look up a range instead of a single value, INDEX/MATCH must be used. VLOOKUP cannot be used to look up a range.

For these reasons, I generally prefer INDEX/MATCH in practically all situations.

Prescott

Once I learned Index/Match, I never went back. You can manipulate your data (especially move columns around) and Index/Match will still work. Unlike VLOOKUP(), the best thing for me is my data doesn't have to be in "left to right" order. You can also do multiple criteria Index/Match much simpler than the VLOOKUP() equivalent, IMO. – Fanchette

In addition, the fact that a VLOOKUP functions points at an entire table of data – even though it only ever actually makes use of two columns of it (the key column on the far left, and the nth column given by the col_index_num argument) – means that any time you change any cell in that table, your VLOOKUP is going to recalculate. And then so are ALL formulas downstream of those VLOOKUPS. It’s exactly as if VLOOKUP is a volatile function. Whereas with INDEX/MATCH, your functions will ONLY recalculate if you change something in the specific columns that the INDEX and MATCH combo references. – Disappoint

Ah yes...it was. Most comprehensive answer I've seen on the topic, by the way. – Disappoint

@Disappoint Thank you. I often see VLOOKUP used in answers on SO at least one of these issues addressed in this answer, so I created this post so that I can refer those users to this answer. – Prescott

For completeness, here's another reason to use INDEX/MATCH: the MATCH part can be split out to it's own cell, and reused (aka 'cached') in the event that you are using multiple VLOOKUPS to bring through additional columns related to a particular record. e.g. if you're looking up a Customer ID, and returning a Surname, a FirstName, and a Gender, say. Splitting out the MATCH component and using it to drive three INDEX functions is going to be upwards of three times more efficient than using three columns populated by VLOOKUP. – Disappoint

@Disappoint This is a great point. See my post, I edited to include this. Thanks! – Prescott

One further reason: you can name the columns. =INDEX(ClientName, XMATCH(@A:A,ClientId)), very nice when looking up data from another sheet. Also - while demonstrating INDEX / MATCH to colleagues relatively recently - stumbled into XMATCH which is even nicer as you don't need the unclear ,0 – Flashback