Looking up values in Excel

While the Microsoft Excel functions LOOKUP and its partners VLOOKUP and HLOOKUP, are especially useful in finding information in tables in spreadsheets, they have their problems.

LOOKUP requires that the list be sorted, otherwise it may find the wrong value.

VLOOKUP and HLOOKUP, can find a value in an unsorted list, but are slow is large tables (for example, I built a table of 65,000 lookups finding values in three tables of 500 each , and Excel took 45 sec to recalculate).

Using INDEX with MATCH is much faster (the same example I built of 65,000 lookups finding values in tables of 500 took 6 secs to recalculate). Also INDEX with MATCH allows a horizontal and vertical lookup at the same time.

The syntax is

=INDEX(array,row_num,column_num)

Array is the range to find the values in and then replace Row and Column with Match and the offset. 

At this point I should point out a robust replacement for the offset. If looking up values in column A to find the value in column B, the simple approach would be to use the offset 2. A better approach is the function COLUMNS(A:B). The result is still 2. If you insert a column between A and B, Excel will not change 2 to 3, but it will change (A:B) to (A:C), with the new result of 3, so it is more robust. Also for us humans, when reading the formula, we know the result is in Column B or C, we don’t have to count the number of columns, much more useful in large worksheets.

So a replacement for the simple

=VLOOKUP(A1,D:E,2,FALSE)

is

=INDEX(D:E,MATCH(A1,D:D,0),COLUMNS(D:E))

This is more robust, if you move columns E it will continue to work, and is much quicker for Excel.

INDEX can also do the two-way lookup, something that the Lookups don’t. For example

=INDEX(D:Z,MATCH(A1,D:D,0),MATCH(A2,1:1,0))

will match A1 in the first column, and A2 in the first Row and return the value at the intersection of the two.