LOOKUP

Using the Excel LOOKUP Function

Summary

The LOOKUP function is considered one of Excel's lookup and reference functions. It's purpose is to review a single row or column and find a specific value, then return a corresponding value from a second row or column.

LOOKUP is useful if you only need to search one row or column. To search more than one row or column, the VLOOKUP function can be used.

LOOKUP can be used in either vector or array form.

Use vector form to search one row or column for a value and when you want to specify the range that contains the value you would like to match.

It is not advised to use the LOOKUP array form. Rather, use the VLOOKUP or HLOOKUP functions. The array form is still provided as a compatibility feature and has limited functionality.

Below we look at the vector form of LOOKUP only.


LOOKUP Vector Form Syntax

=LOOKUP (lookup_value, lookup_vector, [result_vector])

Syntax Breakdown

Lookup Value
Required. The value that LOOKUP will search for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

Lookup Vector
Required. A range containing only one row or column. Values can be text, numbers, or logical values. Values for this argument must be placed in ascending order (i.e. -1, 0, 1 and A-Z).

Text case is not important. For example, Cat would be equivalent to cat.

Excel LOOKUP Text Case Example

Result Vector
Optional. A range containing only one row or column. It is mandatory that this argument be the same size as lookup_vector.


Usage Notes

The LOOKUP function vector form looks in a one-row, one-column range (i.e. a vector) for a value. The function returns a value from the same position in another one-row and one-column range.

LOOKUP will match the largest value in lookup_vector that is less than or equal to lookup_value if it cannot find the lookup_value.

In the below example we can see that the lookup_value, 7, is larger than any of the values in the lookup_vector. The function will find the largest value in lookup_vector that is less than or equal to 7 and return the result_vector.

Excel LOOKUP Large Value Example

LOOKUP returns the #N/A error if lookup_value is less than the smallest value in lookup_vector.

Excel LOOKUP NA Error