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.