Speeding up VLOOKUP

Excel's VLOOKUP formula can run frustratingly slow if you're working with large datasets. It functions by searching each row in an array to see if an exact match can be found when the common VLOOKUP(A2,D:G,4,FALSE) format is used - the FALSE designating an exact match.

The binary version of VLOOKUP using TRUE at the end can work if the data in the searched on array is sorted by the first column. it functions by first checking to see if the searched for value (in the above example A2) is above or below the middle of the search on array. Half of the values to search are ruled out immediately. A VLOOKUP binary search then keeps repeating this process, starting in the middle of one half and excluding a quarter from its review, and so on.

The trouble is that VLOOKUP with the TRUE setting will return the closest possible match - not an exact match. To overcome this limitation, use a VLOOKUP inside a IF . . . THEN formula

=IF(VLOOKUP(A3,I:K,1,TRUE)=A3,VLOOKUP(A3,I:K,3,TRUE),NA())

So instead of this incorrect result returned by a binary VLOOKUP formula:

. . . in the IF . . . THEN formula VLOOKUP first looks to see if the approximate match returns an exact match. If it does it returns that match. If not, it returns a N/A result.

This should function faster than a standard VLOOKUP 'FALSE' formula by several orders of magnitude.