Double VLOOKUP in Excel | Massive Performance Increase

For people with very large data sets who need to use VLOOKUP, this trick could transform the time it takes you to recalculate the spreadsheet I've been working with a
December 18, 2015 - Editor
Category: Other

For people with very large data sets who need to use VLOOKUP, this trick could transform the time it takes you to recalculate the spreadsheet

I've been working with a very large data set in Excel with over 200,000 rows of data, and needing to add attributes to each of the items using VLOOKUP. I used my normal approach with VLOOKUP which was this:

  • Create your lookup table with the first column as the index
  • Sort the table in index key order
  • Name the range for the lookup table
  • VLOOKUP(KeyValue, NamedRange, ReturnColumn, FALSE)

Points on this:

  • It performs the lookup in one step
  • It either returns a result or NA()
  • It uses a linear search into the lookup table, as FALSE means exact match

Moving onwards

VLOOKUP with the 4th parameter set to TRUE does the following:

  • Uses a binary search algorithm into the lookup table
  • Always returns a result even if the KeyValue is not found, giving the 'nearest' match

The binary search is far quicker than the linear search but has the downside of always returning a value. Here is the trick:

  • Result = VLOOKUP(KeyValue, NamedRange, 1, TRUE)
  • This will either return the KeyValue itself or the 'nearest match' value
  • We can tell whether an exact match was found this way: IF (Result = KeyValue, "ExactMatch", "Not Found")

So to combine the binary search approach which is quick, with the need for an exact match, we get the double VLOOKUP:

=IF( VLOOKUP(KeyValue,NamedRange,1,TRUE), VLOOKUP(KeyValue,NamedRange,4,TRUE), NA() )

In English:

IF (key value exact match found on column 1)

THEN

(lookup the return column 4)

ELSE

NA()

ENDIF

If you need more background have a look at this article from which this method is based: https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Consider this a holiday gift ;-)

 


Popular
Most Viewed

Image