LOOKUP

Formulas / LOOKUP
Searches a one-column range for a specific value

LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_value - the value to find
lookup_vector - a range to search (one row or one column)
result_vector - [OPTIONAL] a range of results (one row or one column); if not specified, LOOKUP returns the value in lookup_vector

Examples

=LOOKUP(F4,B5:B9)
The LOOKUP function can be used to find a match in a range of data. This example will return the value of the match found in column B.
=LOOKUP(F4,B5:B9,C5:C9)
The LOOKUP function can also be used to return the corresponding tier from a column. For example, this formula will return the corresponding tier from column C.
=LOOKUP(2,1/(B:B<>""),B:B)
The LOOKUP function can also be used to return the last non-empty cell in a column by using a full column reference. For example, the formula above will return the last non-empty cell in a column.
=LOOKUP(2,1/(item=F5),price)
The LOOKUP function can also be used to return the latest price in data that is ordered by ascending date. This example, will return the latest price in data that is ordered by ascending date.

The LOOKUP function is a lookup function that searches for a value in a range by using the same position in another range.

The LOOKUP function has both array and vector forms and may use a full column as a reference.
Microsoft suggests using VLOOKUP or HLOOKUP for the array form over using the LOOKUP function.
LOOKUP assumes ascending order sorting for the lookup_vector and finds the next smallest value if the lookup_value is not found.
LOOKUP returns #N/A when lookup_value is greater than every lookup_vector value.

What is the LOOKUP function?

The LOOKUP function is a way to find a value in a range by comparing the value to another value in the same range.

What does the LOOKUP function return if it can't find the requested value?

The LOOKUP function returns #N/A if it cannot find the requested value.

Can the LOOKUP function look in multiple rows and columns?

No, the LOOKUP function cannot look in multiple rows and columns like a table.

Is the LOOKUP function case-sensitive?

No, the LOOKUP function is not case-sensitive.

What types of values can the LOOKUP function take as arguments?

The LOOKUP function can take many types of arguments, such as numbers, text, logical values, names, or references.

What does the LOOKUP function return?

The LOOKUP function always finds the last value in a column or row.

What are some other lookup functions?

The VLOOKUP and HLOOKUP functions are newer lookup functions. HLOOKUP and VLOOKUP can index across or down.

Drop CSV