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

Executive summary

Replace your VLOOKUP formula with a combination of INDEX/MATCH. There are only upsides to it – apart from the minor learning effort ;-)!

Quick reference

Replace

=VLOOKUP(YourKey,YourTable,ColumnNumberToReturn,0)

with

=INDEX(ColumnToReturn,MATCH(YourKey,ColumnWithKey,0))

E.g. replace =VLOOKUP(G1,$B$2:$D$5,2,0) with. =INDEX($D$2:$D$5,MATCH(G1,$B2$2:$B$5,0)).

If you need to retrieve multiple columns for one key, extract the MATCH part of the above function to another cell and refer all INDEX functions to this cell.

Detailed description