2 VLOOKUPs are better than 1

The great Charles Williams explains on this blog, Fast Excel, how you can make the popular Excel formula VLOOKUP run faster, by making two VLOOKUPs work together. This approach is helpful whether you're dealing with a worksheet with hundreds of thousands of rows, or if changes are made to a row in an array that requires formulas to be recalculated.

As discussed in last night's tip, a binary search with VLOOKUP, will work faster but it cannot also not be limited to finding an exact match. If you don't want VLOOKUP to find the value that is largest without being greater than the searched for value, you can structure the formula this way:

=IF(VLOOKUP(Y2,L:W,1,TRUE)=Y2,VLOOKUP(Y2,L:W,12,TRUE),"Missing")

The IF formula uses VLOOKUP to see if there is an exact match for the searched for value, and then gives that value as a result if there is an exact match, and if not, will give the result, 'MISSING'.

Some sources report that using VLOOKUP this way may allow for the formula to work more than 3,000 times faster when you have a million or more rows to search through.