Index/Match V Vlookup

[RichardMGreen]

Hi all

Can anyone tell me which is least processor intensive between index/match and vlookup?
We're having a bit of a debate about which is most useful (I'm championing index/match) but can't seem to find a test to check both.

Anyone any ideas?

 

[njimack]

In terms of usefulness, I'd go with INDEX/MATCH every time, simply because it's more versatile. It allows you to return values to the left of your lookup column and you don't need to count columns to figure out which range you want to return.

However, I'd guess it's more processor intensive simply because it's a combo of 2 function (3 if you use MATCH in the row and column arguements of INDEX).

 

[Andrew Poulsom]

You may find this helpful:

http://www.decisionmodels.com/optspeede.htm

 

[T. Valko]

Hi all

Can anyone tell me which is least processor intensive between index/match and vlookup?
We're having a bit of a debate about which is most useful (I'm championing index/match) but can't seem to find a test to check both.

Anyone any ideas?

In general, INDEX/MATCH is more efficient.

However, don't make a mountain out of a mole hill!

If you have a VLOOKUP formula and the equivalent INDEX/MATCH and one formula takes 0.00035 seconds to calculate and the other takes 0.00027 seconds to calculate, can you tell a difference?

So, if your file is 25kb in size with a couple dozen formulas you don't need to be concerned with efficiency.

OTOH, if your file is 20mb with 1000's of formulas then you probably need to be as efficient as possible.

 

[RichardMGreen]

Thanks guys.
It would appear that index/match is more useful but slower (in a nutshell).

 

[Jonmo1]

Actually, Index/Match will likely be faster when used right.

Especially when you also start doing the IF(ISNA(... error checking.
When you do that, you have to do the vlookup twice.
With Index/match, you only do the match twice.
Match is significantly more efficient than Vlookup.


Further..
When you want to return multiple columns based on the same lookup value..
I.E., match A1 to Column B, and return value From C, then D, then E etc..

You put the match in a cell by itself, then base your index off of that cell.
Like so
This is:
1. Simpler because you don't have to incriment the Column Index like in Vlookup
2. Far more efficent because the match is only done once, in B2. The Index formulas then refer to B2.

Excel Workbook
	A	B	C	D	E
1	Lookup Val	Match	Index1	Index2	Index3
2	Fred	1	A	1	Z
3	Betty	4	D	4	W
4	George	#N/A
5	Barney	2	B	2	Y
Sheet1

Excel Workbook
	H	I	J	K
1	Lookup Table	Header1	Header2	Header3
2	Fred	A	1	Z
3	Barney	B	2	Y
4	Wilma	C	3	X
5	Betty	D	4	W
6	Pebbles	E	5	V
7	Bam-Bam	F	6	U
Sheet1

 

[Aladin Akyurek]

Thanks guys.
It would appear that index/match is more useful but slower (in a nutshell).

The Index/Match with match-type set to 0 is a tad faster compared to others
with the same match-type settings. However, if you want fast performance
regarding retrieval operations, arrange the data in ascending order and invoke
look up formulas with LOOKUP or other whose match-type is set to 1.

 

[RichardMGreen]

My data is sorted ascending (I'll always try to do that where even vaguely possible).
It was just a random comment which started the debate between the two methods.

 

[Aladin Akyurek]

My data is sorted ascending (I'll always try to do that where even vaguely possible).
It was just a random comment which started the debate between the two methods.

Let A2:B2000 house the data and A2:A2000 is the match vector
in ascending order. If that order is guaranteed at all times, the following
would be a very efficient set up:

=IF(LOOKUP(G2,$A$2:$A$2000)=G2,
   LOOKUP(G2,$A$2:$A$2000,$B$2:$B$2000),"Not Found")

which assumes that the exact match is needed.