VLOOKUP vs INDEX & MATCH

[shawnmandel]

I was wondering what the differences, benefits, disadvantages of using a VLOOKUP versus the INDEX & MATCH functions in Excel?

Thanks,
Shawn

 

[bmoremu]

From the "Match" definition in Help:

Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

 

[SkipVought]

Shawn,

I think that it's mostly a matter of preference. I use Named Ranges using the column headings. So I perfer to use INDEX & MATCH instead of VLOOKUP, something like this...
[tt]
=INDEX(Amount,MATCH(A2,CompanyID,0),1)
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

One of the main reasons for using INDEX/MATCH over VLOOKUP is that VLOOKUP requires the value being looked up to be in the leftmost column of the table. This prevents you from being able to utilise this feature when the data you want to return from the VLOOKUP is actually to the left of the column that the value is being looked up in. INDEX/MATCH gets you round this. When the data is to the right though, and VLOOKUP is an option, then it is one less function call than INDEX/MATCH, and is therefore more efficient, and if you are looking to scrape every piece of efficiency you can in a large workbook, then it will be important.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[shawnmandel]

Thanks for all of your responses!

Best regards,
Shawn

 

[xlbo]

Have a look here:

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

for a thorough breakdown of the differences between the functions. It's well worth having a proper look around the site as well. There's a LOT of useful info there.

Basically, which you use is pretty much dependant on requirements.

Vlookup - with the 4th arg set to TRUE is quicker than I/M
- with the 4th arg set to FALSE is slower than I/M
- can only return values to the RIGHT of the matched column
- but that tends to propogate good table / spreadsheet design, so not that bad a thing
- is only 1 function and therefore, multiple, nested lookups are clearer to see and debug

INDEX/MATCH - is very flexible
- can return values to the left OR the right of the matched column
- May be hard to read for nested lookups
- Is faster than an absolute match in vlookup but slower than a "closest match"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question