Speed up Vlookup

[KIM Chanthan]

Hi,

Is everyone know any best way to speed up vlookup with exact match. I have big data and I need to do the vlookup, but every time i do, i might spend more hours to wait. It would be appreciate if you can share me the best way to make it fast.

Thanks,
CHanthan

 

[NARAYANK991]

Hi ,

See if this helps :

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Narayan

 

[pecoflyer]

An INDEX/MATCH combination will be much faster
Avoid entire columns

 

[KIM Chanthan]

Hi ,

See if this helps :

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Narayan

hi Narayan,

Thanks for your quick support, but those formula seems doesn't work well. I will miss some data if I use that formula. Please see the attached file for the error.
It would be much appreciated if you have any other way or code.

Thanks,
Chanthan

 

[KIM Chanthan]

An INDEX/MATCH combination will be much faster
Avoid entire columns

I got that, It works faster than vlookup, but it still need time. I also try with VBA, but VBA is slower than even vlookup.

Thanks,
Chanthan

 

[pecoflyer]

Perhaps have a look at Power Query

 

[NARAYANK991]

hi Narayan,

Thanks for your quick support, but those formula seems doesn't work well. I will miss some data if I use that formula. Please see the attached file for the error.
It would be much appreciated if you have any other way or code.

Thanks,
Chanthan

Hi ,

Two points :

For VLOOKUP with TRUE as the fourth parameter , the lookup range has to be sorted.

For VLOOKUP , with either TRUE or FALSE as the fourth parameter , you cannot have duplicates in your lookup range.

Narayan