LOOKUP Function

The LOOKUP function is one of the original lookup functions in Excel. You can use LOOKUP to look up a value in one range or array and return the corresponding value from another range or array. Like the newer XLOOKUP function, LOOKUP can look up values in either rows or columns. However, unlike XLOOKUP, LOOKUP can only perform an approximate match. 

LOOKUP has certain default behaviors that make it useful for solving tricky problems in Excel:

1. LOOKUP always performs an approximate match.
2. LOOKUP assumes that the lookup_vector is sorted in ascending order.
3. LOOKUP can look up values in vertical or horizontal ranges/arrays.
4. When LOOKUP can't find an exact match, it matches the next smallest value.
5. It can handle some array operations without control + shift + enter (in older versions of Excel).

Here is an example of a traditionally difficult problem that LOOKUP has been able to solve for many years: Get value of last non-empty cell. With the introduction of new power functions like XLOOKUP and XMATCH, LOOKUP is not as important as it was in the past, but if you must use an old version of Excel, LOOKUP can still be quite useful.

The LOOKUP function accepts three arguments: lookup_value, lookup_vector, and result_vector. The first argument, lookup_value, is the value to look for. The second argument, lookup_vector, is the range or array to search. The third argument, result_vector, is the range or array from which to return a result. Result_vector is optional. If result_vector is not provided, LOOKUP returns the value of the match found in lookup_vector. The LOOKUP function has two forms: vector and array. Most of this article describes the vector form, but the last example below explains the array form.

Example #1 - basic usage

In the example shown above, the formula in cell F5 returns the value of the match found in column B. Note that result_vector is not provided:

=LOOKUP(F4,B5:B9) // returns match in level

The formula in cell F6 returns the corresponding Tier value from column C. Notice in this case, both lookup_vector and result_vector are provided:

=LOOKUP(F4,B5:B9,C5:C9) // returns corresponding tier

In both formulas, LOOKUP automatically performs an approximate match, so lookup_vector must be sorted in ascending order.

Example #2 - last non-empty cell

LOOKUP can be used to get the value of the last filled (non-empty) cell in a column. In the screen below, the formula in F6 is:

=LOOKUP(2,1/(B:B<>""),B:B)

Note the use of a full column reference. This is not an intuitive formula, but it works well. The key to understanding this formula is to recognize that the lookup_value of 2 is deliberately larger than any values that will appear in the lookup_vector. Detailed explanation here.

Example #3 - latest price

Like the above example, the lookup function can be used to look up the latest price in data sorted in ascending order by date. In the screen below, the formula in G5 is:

=LOOKUP(2,1/(item=F5),price)

where item (B5:B12) and price (D5:D12) are named ranges. 

When lookup_value is greater than all values in lookup_array, the default behavior is to "fall back" to the previous value. This formula exploits this behavior by creating an array that contains only 1s and errors, then deliberately looking for the value 2, which will never be found. More details here.

Example #4 - array form

The LOOKUP function has an array form as well. In the array configuration, LOOKUP takes just two arguments: the lookup_value, and a single two-dimensional array:

LOOKUP(lookup_value, array) // array form

In the array form, LOOKUP evaluates the array and automatically changes behavior based on the array dimensions. If the array is wider than tall, LOOKUP looks for the lookup value in the first row of the array (like HLOOKUP). If the array is taller than wide (or square), LOOKUP looks for the lookup value in the first column (like VLOOKUP). In either case, LOOKUP returns a value at the same position from the last row or column in the array. The example below shows how the array form works. The formula in F5 is configured to use a vertical array, and the formula in F6 is configured to use a horizontal array:

=LOOKUP(E5,B5:C9) // vertical array
=LOOKUP(E6,C11:G12) // horizontal array

The vertical and horizontal arrays contain the same values; only the orientation is different.

Note: Microsoft discourages the use of the array form and suggests VLOOKUP and HLOOKUP as better options.

Notes

• LOOKUP assumes that lookup_vector is sorted in ascending order.
• When lookup_value can't be found, LOOKUP will match the next smallest value.
• When lookup_value is greater than all values in lookup_vector, LOOKUP matches the last value.
• When lookup_value is less than the first value in lookup_vector, LOOKUP returns #N/A.
• Result_vector must be the same size as lookup_vector.
• LOOKUP is not case-sensitive