Speedup your vlookup: 2vlookups instead of 1 vlookup

Simple VLOOKUP formula is reasonably faster with small table data.  But in case if you are handling a large data table like 10,000 or 1,00,000 rows, VLOOKUP may be bit slower. To speedup your VLOOKUP you can use below technique which will improve the speed by more than 10 times.  We may not understand how exactly it works  in the back end, but it really works.

In VLOOKUP, there are two types of matching which we will have to define in the formula.  One is approximate match, which will do the binary search which is much faster . The other one is Exact match which does the linear search which is slower than the first one.

VLOOKUP WITH EXACT MATCH

VLOOKUP WITH APPROXIMATE MATCH

If the range lookup is FALSE, VLOOKUP will do exact match. If the range lookup is TRUE, VLOOKUP will do Approximate match.

Approximate match formula is faster, but if there are no matching value in table, it will show next bigger nearest matching number. Sometimes this may be misleading. Vlookup with approximate match is dangerous. 

However, with the help of IF function, we can use approximate match in VLOOKUP to bring exact matching data.

2vlookups instead of 1 vlookup with IF function.

The first step in this is to sort the table, since we are using approximate match.

In this formula we will have to use 2 VLOOKUP function instead of one VLOOKUP. If first VLOOKUP approximate match result is matching exactly with the lookup value, then it will give the result of  second VLOOKUP approximate match. If it is not exactly matching, then it will not give the result. You can see the below formula for easy understanding.

Speedup your vlookup
=IF(VLOOKUP(F6,C2:D52,1,TRUE)=F6,VLOOKUP(F6,C2:D52,2,TRUE),"Not Available")

In case if the lookup value is not available in table, it will show “Not Available“. This formula will also help in error handling in excel. You need not use IFERROR to handle error.

Leave a Reply

Your email address will not be published. Required fields are marked *