Index vs VLookUp

[rvExcelTip]

Can somebody tell me the relative strength and weaknesses of INDEX v
V/HLookUp

 

[Frank Kabel]

Hi
just my 2 cents:
- The VLOOKUP function may be a little bit faster than the combination
of INDEX/MATCH
- VLOOKUP requires that your lokkup value is in the leftmost column of
your range. If this is not the case you have to switch to INDEX/MATCH

 

[Charles Williams]

See http://www.DecisionModels.com/optspeede.htm for a discussion of this
together with tips on lookups.


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[rvExcelTip]

Found on decision models web page:

-INDEX is very fast and from Excel 97 onwards is a non-volatil
function (speeds up recalculation).
OFFSET is also very fast, but it’s a volatile function.-

Do you have any idea if the MATCH function is volatile?

I suppose volatile means that if you have the same formula twice in tw
different cells it is calculated only once? e.g a same MaTCH could b
embedded in different INDEX formula

 

[Peo Sjoblom]

Match is not volatile although I doubt a combination of index and mat is
faster than vlookup