Vlookup vs Index/Match

[iknowu99]

Hello All,

I have seen awhile back formulas that would emulate vlookup and would lookup not only to the right of the table array but also to the left and anywhere, i believe they used index formula and match formula in combination and it would act like vlookup/hlookup. Which way is faster for excel to calculate?

 

[Jonmo1]

Oops, I accidentally voted for Vlookup, meant to vote for Index/Match.
Can a moderator correct it?

 

[iknowu99]

jonmo1 you the only one :D

for others, please see :
http://www.mrexcel.com/forum/showthread.php?t=339373

 

[NateO]

Ask yourself this, which of the two commits the smaller array to memory.

There's your answer.

Edit: I might have to rethink that, Vlookup() would make one memory commitment, and Index(Match()) would make two. When in doubt, use a timer.

Still, you have to love the flexibility of Index(Match())

 

[Domski]

I think the two of them have their place.

If I want to quickly populate a column of data based from one column in another table of data I will often use Vlookup. It's quicker to type as well.

On the other hand Index and Match is far more flexible. One of my main uses for it is adding multiple columns of info from one table into another. In this case I will do the match in one column and then use multiple Index formulas in the other. Another advantage of match is that it has the 3 possible switches (-1,0,1) where Vlookup is limited to just the 2 (True, False).

Dom

 

[SydneyGeek]

Definitely horses for courses. If you need to look left, it's a no-brainer. If you are pulling data from many columns away, INDEX/MATCH too. For narrow tables, VLOOKUP is pretty good.
I also find that teaching VLOOKUP is easier than INDEX/MATCH -- it can take a bit to get your head around it initially.

And... INDEX/MATCH as an array lets you find the first non-zero value (eg, break-even point in a cash flow). So for flexibility it's the winner for me.

Denis

 

[Cbrine]

While I agree that the index match combination is much more flexible than the vlookup(left lookups, and multiple criteria), I would have to agree with Denis. I've had to many nightmares with updating the range of the index, but forgeting to update the match portion....ends up with valid looking results, that are totally wrong. (PS-I know that named ranges would eliminate this issue, but I didn't use them at the time I learn the index/match, and now I'm addicted to vlookup)

 

[DigitalHik]

I think both are extremely useful tools in their respective contexts. For pulling data from pivot tables into reports, Vlookup is definitely the way to go. For pulling data into a chart or report based on one key field of data, then by all means, Index.

 

[TinaP]

Vlookup when I can; Index/Match when required.

 

[schielrn]

On a side note, on other boards like this that have polls, there is an option to see who has voted for which topic, is it just that the poll creator has disabled this option or is it not available (I have not created a poll so I don't know)?