VLOOKUP with exact match and sorted data
VLOOKUP formulas where you want an exact match are often accused of being slow, especially when the lookup table has a large number of rows and columns. And if you have a lot of VLOOKUP formulas, changing a value in the lookup table can result in an annoying delay while all the VLOOKUP formulas using that table recalculate.
In many cases, the user would be willing to sort the lookup table by the first column--provided that led to a significant improvement in recalc time because of a binary search. The problem is that setting the fourth VLOOKUP parameter to TRUE results in unexpectedly returning a wrong value if the search value (first parameter) isn't found.
What I would like is the following:
=VLOOKUP("abc", A2:Z1000, 3, FALSE) check each row in column A for match, column A need not be sorted; exact match required Existing functionality
=VLOOKUP("abc", A2:Z1000, 3, SortedExact) binary search through column A for match, column A must be sorted in ascending order; exact match required New capability
=VLOOKUP("abc", A2:Z1000, 3, TRUE) binary search through column A for match, column A must be sorted in ascending order; exact match not required (i.e. bracket matching) Existing capability
Maybe converting the range A2:Z1000 into an "Excel table", but adding to "Excel tables" the option of indexing the first column (similar to Access). This way, both using FALSE and TRUE, will always work fast. Moreover, adding an "automatic column sorting" option to "Excel tables" could be interesting.
I need vlookup/hlookup to not ONLY search in the first row/column..
Kenneth Barber commented
The current workaround for the new capability is to do this: IF(VLOOKUP("value",A:A,1,TRUE)="value",VLOOKUP("value",A:C,COLUMNS(A:C),TRUE),NA()).
The first VLOOKUP checks if the sorted match is actually a match. The second VLOOKUP is your regular VLOOKUP (3 changed to COLUMNS(A:C) for robustness).