Vlookup vs Index/Match

iknowu99

Well-known Member

Joined

Dec 26, 2004

Messages

1,158

Office Version

1. 2016

• Sep 2, 2008

• #1

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

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Sep 2, 2008

• #2

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

 

iknowu99

Well-known Member

Joined

Dec 26, 2004

Messages

1,158

Office Version

1. 2016

• Sep 2, 2008

• #3

jonmo1 you the only one :D

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

 

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Sep 2, 2008

• #4

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())

 

Last edited: Sep 2, 2008

Domski

Well-known Member

Joined

Jan 18, 2005

Messages

7,292

• Sep 2, 2008

• #5

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

MrExcel MVP

Joined

Aug 5, 2003

Messages

12,251

• Sep 3, 2008

• #6

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

 

Last edited: Sep 3, 2008

Cbrine

Well-known Member

Joined

Dec 2, 2003

Messages

3,196

• Sep 3, 2008

• #7

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)

 

D

DigitalHik

New Member

Joined

Sep 3, 2008

Messages

0

• Sep 3, 2008

• #8

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

Well-known Member

Joined

Jan 26, 2005

Messages

528

• Sep 3, 2008

• #9

Vlookup when I can; Index/Match when required.

 

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Sep 3, 2008

• #10

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)?