Image
December 18, 2015

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 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

Related Articles


September 15, 2022

Tradefeedr Hires Alexis Fauth as Head of Data Science and Client Analytics




2 MIN



FX


September 6, 2022

Siege FX announces the launch of NetFix




2 MIN



FX


August 2, 2022

OSTTRA and LCH collaborate to reconcile bilateral OTC trade data




2 MIN



Post Trade Processing