Vlookup vs Index
Can someone kindly provide me with some understanding, what are the difference between vlookup and index ?
Which can actually find more values or they are equally the same. I'm currently using index. However i still hear debates regarding about vlookup.
Thanks in advanced.
Hi,
The Main difference is that VLOOKUP requires the match to be to the Right (Columns) of the Lookup Value, where INDEX/MATCH can be Left, Right, Up, or Down.
By the same token, HLOOKUP requires the match to be Below (Rows) of the Lookup Value, and INDEX/MATCH does not.
In a nutshell, vlookup cannot look "behind" itself, your search criteria must be in the 1st column of the search range. Also, in it's purest form, you need to know the column number to return (although this can be overcome using MATCH)
INDEX returns the cell contents at the intersect of 2 2-d ranges, and as such, that intersect could be anywhere in the index range.
I use VLOOKUP if I an working with a simple table of, say, name and phone number, I have a name and need the number.
I would use INDEX (with MATCH) with a large table where I need a more complex search feature
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
INDEX MATCH in its most basic form uses just two columns, whereas VLOOKUP might involve a very wide array, and often requires you to count columns in order to set it up, so unless it's a simple side-by-side lookup, I'd be inclined to use INDEX MATCH.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Think of vlookup as the simple version of index & match, there for novice users who don't understand how to nest functions. Although, given how many questions there are on the forum with novice users trying to use vlookup incorrectly, referring to it as simple could be consedered a misnomer.
Apart from less typing / thinking in very basic use, such as Ford's example
I can't think of any advantage to vlookup.Originally Posted by FDibbins
Anything vookup can do, index & match can do (and faster).
I do disagree with the comments that vlookup cannot look behind itself though, there is a way, but a pointless one. Index and match would be easier.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks