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