VLOOKUP vs INDEX/MATCH?

[parsec]

Hello to all
I would like to here your opinion about the VLOOKUP vs INDEX/MATCH function.
Is one faster than the other, what is the difference, and what it will be the criteria to use one over the other?

I have a sheet with several VLOOKUP formulas, it takes a long time to get updated also when I save it takes a long time what causes this, any way to remedy this?

Thank you
All
John

 

[Aladin Akyurek]

When Vlookup is not applicable (as with when you need "left lookup"), Index/Match is what you need.

In your case, the speed is the issue. There won't be any significant difference because VLOOKUP and MATCH invokes the same routines.

If you retrieve data from multiple columns wrt a lookup value, Index/Match gives you a more efficient set up.

Why don't you post your VLOOKUP formula so that we can see what you're dealing with?

 

[parsec]

Thank you
here is my formula:

=VLOOKUP(A2,'I:\NATURES MARKET WORK SPACE\WORKING IN\VENDORS CATALOGS\STOW\STOW CATALOG\[CHE Price List run on 3-3-04.xls]Sheet1'!Print_Area,8,0)

Shall I provide some more info about the sheet?

Sorry I had to wait for Excel to save the file before I had a chance to open it again it really takes along time to save?

Thank you

John

 

[Aladin Akyurek]

Thank you
here is my formula:

=VLOOKUP(A2,'I:\NATURES MARKET WORK SPACE\WORKING IN\VENDORS CATALOGS\STOW\STOW CATALOG\[CHE Price List run on 3-3-04.xls]Sheet1'!Print_Area,8,0)

Shall I provide some more info about the sheet?

Sorry I had to wait for Excel to save the file before I had a chance to open it again it really takes along time to save?

Thank you

John

What does Sheet1!Print_Area refer to?

 

[parsec]

it is the array that I have set up as a table,
Thank you

 

[just_jon]

I believe Aladin was refering to what range on the sheet is incorporated by Print_Area -- as in A1:Z100, etc.

 

[parsec]

Sorry

A1:M5000

Thank you

 

[just_jon]

OK; I'm going to leave you in Aladin's more-than-capable hands. He's very good at supercharging formula/range sets.

 

[Aladin Akyurek]

...
A1:M5000...

In how many cells do you have the formula

=VLOOKUP(A2,'I:\NATURES MARKET WORK SPACE\WORKING IN\VENDORS CATALOGS\STOW\STOW CATALOG\[CHE Price List run on 3-3-04.xls]Sheet1'!Print_Area,8,0)

And are you only retrieving from the 8th column of Print_Area, the lookup table?

 

[Ekim]

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

VLOOKUP is slightly faster (approx. 5%), simpler and uses less memory than a combination of MATCH and INDEX or OFFSET.
However the additional flexibility offered by MATCH and INDEX often allows you to make significant timesaving compared to VLOOKUP.
INDEX is very fast and from Excel 97 onwards is a non-volatile function (speeds up recalculation).
OFFSET is also very fast, but it’s a volatile function.

I have no test data to support the above assertions. You may wish to setup a comparison workbook with INDEX/MATCH and compare same with your VLOOKUP formulas.

In case you are not familiar with INDEX/MATCH, the following return the same answer (example from the above reference):
VLOOKUP(A1,Data!$A$2:$F$1000,3,False)
INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

The reference also suggests a number of strategies to speed-up lookups.

HTH

Mike