Speeding up VLOOKUP (and MATCH) formulas by orders of magnitude!

Written by Peter Albert on . Posted in Data handling, Expert, Modeling

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

  1. Sort your data ascending according to your key column (the first column of your VLOOKUP range)
  2. Replace your =VLOOKUP(A1,Sheet2!$A:$B,2,0) with =VLOOKUP(A1,Sheet2!$A:$B,2) (you can omit the TRUEas last parameter as this is the default)
  3. 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())

Detailed explanation