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?

Thanks!

 

[RoryA]

I prefer INDEX and MATCH as you only have to reference the two columns that interest you and not an entire table range. If you want to lookup multiple columns for a given row, you can also store the MATCH part in one cell and reference that number in multiple INDEX formulas so you only do the lookup part once, which is more efficient. If you want to lookup to the left, you can't use VLOOKUP anyway.
HTH

 

[iknowu99]

i will post this in the chat and run a poll

 

[theozz]

I think...

If Same Condition...

Slow - Middle - Fast

vlookup/hlookup - index+match - lookup - offset + match

 

[Jonmo1]

i will post this in the chat and run a poll

I think you will find most prefer Index/Match over Vlookup.

1. It CAN go to the left
2. You only reference 2 columns instead of an entire 2D Range.

Example of how it is written

=VLOOKUP(A1,B:G,6,FALSE)
This references a 6 Column Range(65536 X 6 = 393216 Cells Loaded to memory)
And can only go to the right..

=INDEX(G:G,MATCH(A1,B:B,0))
This only references 2 1D Ranges (2 X 65536 = 131072 Cells Loaded to memory)

to make it go left
=INDEX(B:B,MATCH(A1,G:G,0))

Hope that helps...

 

[iknowu99]

thanks for that Jonmo1. This means even in VBA the index/match combination is better:

 Application.WorksheetFunction.

syntax