LOOKUP

Excel Functions

Look up a value in a one-column range

Syntax

EXCEL

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Arguments

Parameter	Description
`lookup_value`	The value to search for.
`lookup_vector`	The array or range to search.
`result_vector`	[optional] The array or range to return.

Return Value

A value in the result vector.

Details

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: 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 fun

Examples

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:

EXCEL

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

Example #1 - basic usage

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:

EXCEL

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

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:

EXCEL

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

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, th

EXCEL

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

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-dimensi

EXCEL

LOOKUP(lookup_value, array) // array form

Example #4 - 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, LOOKU

EXCEL

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

LOOKUP

Syntax

Arguments

Return Value

Details

Examples

See Also