Speeding up VLOOKUP (and MATCH) formulas by orders of magnitude!
Executive summary
By default, you’re using FALSE
as the last parameter of a VLOOKUP or MATCH formula to make sure that the proper result is returned. This approach is really slow on larger data sets. Alternatively, sorting the data and using TRUE
will increase the overall search speed by a very large factor!!!
This post will explain how and when to use this technique – and what to watch out for.
Quick reference
- Sort your data ascending according to your key column (the first column of your VLOOKUP range)
- Replace your
=VLOOKUP(A1,Sheet2!$A:$B,2,0)
with=VLOOKUP(A1,Sheet2!$A:$B,2)
(you can omit theTRUE
as last parameter as this is the default) - Watchout: In case you are not sure if
A1
is contained in your list, use this formula:=IF(VLOOKUP(A1,Sheet2!$A:$B,1)=A1,VLOOKUP(A1,Sheet2!$A:$B,2),NA())