Help! Excel VLOOKUP is so DAMN SLOW!

Warning: This post assumes you know the basics of Excel VLOOKUP.

By default, Excel automatically re-calculates formulas in the spreadsheet.

Problem is if you have a VLOOKUP against 100k+ rows, it's gonna be a long day for you at the office.

Here's how to fix that.

Step 1: Sort your table_array data.

Step 2: Change your formula from

=VLOOKUP (lookup_value, table_array, col_index_num, FALSE)

To this

=IF( 
  VLOOKUP (lookup_value, table_array, 1, TRUE) = lookup_value
, VLOOKUP (lookup_value, table_array, col_index_num, TRUE)
, NA()
)

See, we're using an IF statement now.

The condition is a VLOOKUP using APPROXIMATE-MATCH (1/TRUE) which is faster than EXACT-MATCH (0/FALSE).

If the approximate match is equal to the lookup value, then you basically have an exact match!

So...

If TRUE, then do the VLOOKUP and return the value at col_index_num.

If FALSE, return #N/A.

Life hack!