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.

Making VLOOKUP run faster

Here's a follow-up on last night's tip, which referred to running binary searches in Excel. Usually when I use the VLOOKUP formula, the fourth 'setting' in the function will be FALSE (after the searched for value, the array; and the return value), indicating that there must be an exact match. Entering TRUE instead of FALSE will cause VLOOKUP to search for an approximate match. If VLOOKUP is set with TRUE, the data in the first column of the array must be sorted in ascending order. If this is not done, the function may return an incorrect result. When the data is sorted in an ascending order, a large percentage of the cell values can be skipped over allowing the function to run more quickly.

When TRUE is entered in a VLOOKUP function, it will return an exact match if it's present, but if it's not, it will simply return the largest value that is less than the searched for value. See this example in which I'm using VLOOKUP with the TRUE setting to search for a number of home runs hit in a season. Barry Bonds hit 73 in one season and Mark McGwire hit 70, but no player has finished a regular season with 71 or 72 home runs. So when we search for 72, the result should be 70. When the data in the home run column is not sorted in ascending order, an erroneous result is returned.

When the spreadsheet is sorted with home runs in ascending order, we get the correct result.

There is an advantage to using the TRUE setting, even if you want to only find an exact match. VLOOKUP will work far more quickly with TRUE setting than with a FALSE setting. If you're working with a spreadsheet with hundreds of thousands of rows, shifting to the TRUE setting may make the function run hundreds of times faster.

XLOOKUP

Take note that Microsoft is planning to release a revised version of its workhouse VLOOKUP function for MS Excel. The new function is called XLOOKUP. It's currently only available for Office 365 subscribers with special insider status, but it should be released more widely before the end of the year.

XLOOKUP follows this basic format:

=XLOOKUP(value, array, return_array, match setting, search setting)

No longer will users have to specify the number of the column in a long array from which a value should be returned. The new XLOOKUP function also allows for fuzzy searches to be run.

The first entry in the formula, the 'value', is a reference to the cell containing the string you want to look up - just like with VLOOKUP.

The second entry is the column where the value to be found is located. 'C1:C100', or whatever.

The third entry is the column where the value to be returned can be located. E.g., 'Z1:Z100'. It's not necessary to enter an array C1:Z100, and then count how many columns Z is from C. Another improvement over VLOOKUP is that the value to be returned can be in a column to the left of the value being looked up. E.g., in 'B1:B100'.

The fourth entry is not limited to the kind of match settings in VLOOKUP - either TRUE or FALSE. A zero here will require an exact match. '-1' will find an exact match or a number closed to the search for value that is not larger than it. '1' will find an exact match for a number or the number that is closest to it without being smaller. A '2' setting will provide a real innovation in allowing for a fuzzy search to be performed.

The fifth entry is for the 'search mode'. A '1' runs a search for the first occurrence of the looked up value starting at the top of the column in the lookup array. A '-1' runs a search for the first occurrence of the looked up value starting from the bottom of the column in the lookup array. A '2' or '-2' will serve the same purpose, but cause a binary search to be run which is quicker - it relies on the data being sorted in ascending or descending order.