By Dany Hoter, Datarails Solutions Architect
In most of the cases I have seen when users had performance issues with Excel, the VLOOKUP function was the culprit.
In a previous post I talked about replacing many VLOOKUP functions with one match function and multiple index functions. It is a good method when you use VLOOKUP to bring multiple columns from the same table.
This is not always the case, however, and often the sheer number of rows on which the VLOOKUP functions are repeated and the size of the lookup table is so big that even bringing one column can take minutes. Using a match/index combination will not be any faster in bringing one column.
Ten years ago I figured out a way to speed up VLOOKUP significantly, and I have been telling people about it since. In some cases, calculation time dropped from hours to seconds. During those years, I discovered that I was not the sole inventor of this trick. Charles Williams described the same mechanism in a detailed post from 2012.
In this article, I will share how to change the existing VLOOKUP formulas, as explained in the article, from one VLOOKUP using false as the last argument to two VLOOKUP functions both using true as the last argument.
If the VLOOKUP functions are found in simple table or table-like regions in Excel, you can simply fix the formula in the first row and copy down to all other rows. In some cases, the formulas are in regions that have blank rows or rows with different formulas that you cannot override. If so, the method I explain in a previous post using R1C1 notation can be very useful.
This file has VLOOKUP functions, but I used the subtotal feature to add subtotals for each customer. In this case, you can’t just copy down the formulas from the first row.
If you switch to R1C1 notation, the VLOOKUP function will become
=VLOOKUP(RC1,Customers,2,FALSE)
instead of
=VLOOKUP($A2,Customers,2,FALSE)
Now you can use “replace all” to replace
=VLOOKUP(RC1,Customers,2,FALSE)
with
=IF(VLOOKUP(RC1,Customers,1,TRUE)=RC1,VLOOKUP(RC1,Customers,2,TRUE),”Not Found”)
If you have similar VLOOKUPs that bring other columns from the customers table, you can repeat the process by changing the column number from two to any other column that you need. Just leave the first VLOOKUP in the final result to bring the first column. The end result should be like in this file.
If you want to appreciate the difference in calculation speed, switch to the customers sheet and change the name for the first customer. Because all VLOOKUP functions reference the entire table, all of them are calculated when you change even a single cell.
In the slow example, after you apply the change and hit “enter,” the cursor will remain on the cell for a few seconds. In the fast example, the cursor will immediately move forward, although all VLOOKUP functions are still calculated.
In this case, I used two VLOOKUP functions on ~60,000 rows against a ~18,000 lookup table.
If you increase these proportions, the calculation time will grow accordingly in the slow example and will hardly change in the fast example.
About the author:
Dany Hoter is a renowned Excel guru who works as a solutions architect at Datarails. Dany has over two decades’ experience working as an expert for the Microsoft Excel team, and his online Excel classes reach over one million students across the globe.