Perform faster VLOOKUP on Large Data

While applying VLOOKUP on large data (let’s say 100k rows or more) you often get beaten down by extremely slow spreadsheets or even sometimes by this sweet notification..

Yeah I know we say all types expletives.. #$%k me!, Oh Jeez..

One of the other ways to speedup is to use PowerPivot (which is heavenly!!) but lets say for some reason you still have not made a transition to PowerBI suite in that case let’s together explore this little trick..

The incredible part is that it is insanely simple!

 

The Standard Vlookup.. (with exact match)

1. Goes and checks in each cell of the first column of the LargeDataSet
2. And because it checks in each cell it becomes incredibly slow when you work with a Large Data

 

The Approximate Match VLookup.. (true in the end)

1. If you don’t know it already then I must tell you that approximate vlookup is a hell lot faster than exact match vlookup. The reason is because it works with the sorted data (in ascending order)
   • Consider this analogy : The reason it is easier for you to find any word in the dictionary is beacuse it is sorted, in lame terms if the data is sorted in the ascending order it becomes easier for vlookup too to find and fetch the data
2. But here is the catch – Why don’t people use it if it is that fast? It is because approximate vlookup can return a value (wrong answer) even if the value does not exist in the LargeDataSet, because it is making an approximate match

 

So the Trick is..

To apply If + Vlookup with approximate match..

1. Above we have 100k rows of Data and VLookup has to be performed on 10k rows
2. Note that a few codes are missing from the Large Data (and that is done on purpose). So ideally Vlookup should not pull those records and should instead show an #N/A

 

Here comes the VLookup formula..

Click the picture to enlarge it

 

=IF(Code=VLOOKUP(J5,LargeDataSet,1,TRUE),VLOOKUP(Code,LargeDataSet,2,TRUE),NA())

Decoding the formula

1. The IF formula checks if the Lookup Value is the same as the value found by Vlookup
2. If the value is same the IF formula performs the approximate match Vlookup
3. Else it returns the #N/A error!

Did you see the damn speed of that.. I was flabbergasted!

 

DOWNLOAD THE EXCEL FILE HERE

 

If you are more of a Video learner .. here is a quick video explaining the trick

There is a thing that I missed in the video – 

• Please note that Approximate Match Vlookups only work correctly with Sorted Data
• The LargeDataSet is sorted in ascending (a-z) order

 

I originally learnt this trick from Charles Williams from Fast Excel

 

Download The Ultimate VLOOKUP Guide

The guide contains tricks and tips beyond the conventional VLOOKUP and it’s free – DOWNLOAD THE ULTIMATE VLOOKUP GUIDE (pdf)

 

More Vlookup Tricks..

1. Learn Vlookup from Scratch
2. 3 Most Common Vlookup mistakes – VLOOKUPhobia
3. 5 Vlookup Tricks in Excel
4. Make your Vlookup Robust
5. How to Lookup similar but not matching records
6. Picture Vlookup in Excel

 

If you work with large data sets,  what do you think about it? Put down your thoughts in the comments