Is Index/Match Faster when just looking up one column?

[snipescc]

I'm doing a massive number of Vlookups on an old computer running the latest version of Excel . I've heard both that index/match is faster, and that it used to be, but isn't much anymore. Some vlookups are only in one column, some will be a few columns wide. I'm more comfortable with vlookup that Index/Match, but considering the hundreds of thousands that I'm doing, can anyone tell me if it will be worth switching?

 

[Jonmo1]

I think the biggest performance advantage index/match will have over vlookup is when you're doing multiple lookups based on the same lookup value..

i.e.
You do this
=VLOOKUP(A1,othersheet!$A:$Z,2,0)
But then repeating the exact same formula changing the 2 to 3 to 4 etc..
=VLOOKUP(A1,othersheet!$A:$Z,3,0)
=VLOOKUP(A1,othersheet!$A:$Z,4,0)

In this scenario, vlookup is doing the work of finding the value of A1 in othersheet!A:A 3 times.

If instead, you do this
in B1 put
=MATCH(A1,othersheet!A:A,0)
Then use
=INDEX(othersheet!B:B,$B1)
And fill right.

Then the work of finding A1 in othersheet!A:A is only done once.
The result of that work is then only referenced by the index formula.

That will make a HUGE performance improvement.

 

[joeu2004]

I cannot imagine why index/match would be faster than vlookup. Conceptually, it should be just the opposite. So off-hand, I would say: no, it is not worth switching.

But it might depend on details that you neglected to provide, namely example vlookup and index/match formulas.

Moreover, you say "some vlookups are ... a few columns wide". That makes no sense. VLOOKUP and MATCH search only one column; in the case of VLOOKUP, it is the first column of the second parameter.

 

[snipescc]

That's a good point. So I add another column, and all the other lookups just look at that number. Nice.

 

[joeu2004]

I cannot imagine why index/match would be faster than vlookup.

In this scenario, vlookup is doing the work of finding the value of A1 in othersheet!A:A 3 times. If instead, you do this
in B1 put
=MATCH(A1,othersheet!A:A,0)
Then use
=INDEX(othersheet!B:B,$B1)
And fill right.

I certainly agree with that.

 

[Jonmo1]

To be fair and recognize joeu2004's point..
A single index/match formula will certainly not be faster than a single vlookup formula.

However, depending on how they are used
1000 index/match formulas can indeed be faster than 1000 vlookups.

 

[Marcelo Branco]

I cannot imagine why index/match would be faster than vlookup. Conceptually, it should be just the opposite. So off-hand, I would say: no, it is not worth switching.

See this post by Charley Kid where in the summary (final notes) he says:
"At its worst, the INDEX-MATCH method is about as fast as VLOOKUP; at its best, it’s much faster."

http://www.exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html

M.

 

[Jonmo1]

Another way to improve performance of vlookup or index/match (I'll use vlookup for this example)
Is if your data can be sorted in Ascending Order.

If you can do that, then performance can be improved immensely

Using the same vlookup from previous example =VLOOKUP(A1,othersheet!$A:$Z,2,0)
With Data Sorted Ascending by column A on othersheet, you can do this
=IF(VLOOKUP(A1,othersheet!$A:$Z,1,TRUE)=A1,VLOOKUP(A1,othersheet!$A:$Z,2,TRUE),NA())


YES, 2 vlookups using TRUE as the 4th argument will be many (MANY) times faster than 1 vlookup using FALSE.


The same can be done with index/match, from my example above you would use 1 instead of 0 in the match.
=MATCH(A1,othersheet!A:A,1)