vlookup choose vs index match

[hdavis3944]

I just saw that someone used the option of using vlookup with choose nested inside it to look at fewer columns and look left. I know I've been told many times that index match is a great option for this because it is quicker and more efficient. Now, I am wondering which is more efficient if there is even a difference. We are now only looking at a range now set forth by choose so excel is no longer having to look at a larger data set. I am always trying to clean up my code and if this option is better then I'm for it. I tend to over code my sheets and then have to close out all excel and open this one file up so that it will run properly. If I can avoid this now that would be great!
Thank you for your help,

Henry Davis

 

[arthurbr]

Depending on the authors it seems that INDEX/MATCH is faster than VLOOKUP. Although with small ranges it won't show.

I find that this combination is much more flexible than VLOOKUP is so I stick to it whenever I can

 

[RoryA]

INDEX + MATCH should definitely be faster than VLOOKUP and CHOOSE, as well as being more intelligible. I've only ever regarded VLOOKUP + CHOOSE as a "proof of concept" rather than something you would ever actually use.

 

[Aladin Akyurek]

VLOOKUP requires left to right evaluation. Also, we need to specify a column index.

(1) VLOOKUP(lvalue,Table,N,[0/1])

lvalue must be in the first column of Table, i.e. INDEX(Table,0,1). The match-type is either exact, i.e. 0, or approximate, i.e. 1.

N is either calculated (desirable) or hard-coded (troublesome).

The equivalent INDEX|MATCH of [1] is...

(2) INDEX(INDEX(Table,0,N),MATCH(lvalue,INDEX(Table,0,1),[0/1]))

Suppose we have:

(3) INDEX(INDEX(Table,0,2),MATCH(lvalue,INDEX(Table,0,N),[0/1]))

What would be the VLOOKUP equivalent of [3]? That is here that CHOOSE comes in...

(4) VLOOKUP(lvalue,CHOOSE({1,2},INDEX(Table,0,N),INDEX(Table,0,2)),2,[0/1])

where N >= 2.

Concrete examples for [3] and [4]...

=INDEX(F2:F100,MATCH("JAD",X2:X100,0))

=VLOOKUP("JAD",CHOOSE({1,2},X2:X100,F2:F100),2,0)

The forgoing two are equivalent.