Summary

The LOOKUP function in Excel is used for searching one row or one column for a value and retrieving a corresponding value from another row or column. It’s a basic lookup function that can be utilized for simpler lookup tasks compared to VLOOKUP or HLOOKUP. LOOKUP function comes in two forms: vector and array.

Syntax

				
					=LOOKUP(lookup_value, lookup_vector, [result_vector]) //vector form
=LOOKUP(lookup_value, array) //array form

lookup_value: The value to search for.
lookup_vector: The range containing the value to look for.
[result_vector]: [Optional] The range containing the result to return.
array: A two-row or two-column range of cells containing pairs of lookup values and result values.

Return value

Returns the value found in the result_vector or array that corresponds to the lookup_value.

How to use

The vector form is used when you have separate ranges for the lookup values and the result values. The array form is used when the data is in a single range that contains both lookup and result values.

Examples

LOOKUP in Vector Form

Basic One-Dimensional Lookup: Finding a corresponding value in a separate range:

				
					=LOOKUP("Apple", A2:A5, B2:B5)

This formula searches for “Apple” in the range A2:A5 and returns the corresponding value from the range B2:B5.

LOOKUP in Array Form

Two-Dimensional Data Lookup: Retrieving a result from a two-row array:

				
					=LOOKUP(3, A1:B2)

Assuming the first row contains lookup values and the second row contains results, this formula finds the number 3 in the first row and returns the corresponding value from the second row.

LOOKUP for Approximate Match

Approximate Match in a Sorted List: Searching for the nearest lower value in a sorted list:

				
					=LOOKUP(250, A2:A10, B2:B10)

This formula looks for the number 250 in a sorted range A2:A10 and returns the corresponding value from B2:B10 that’s closest to and less than 250.

LOOKUP with Unsorted Data

Lookup in Unsorted Data: Using LOOKUP when the data isn’t sorted:

				
					=LOOKUP("Orange", CHOOSE({1,2}, A2:A5, B2:B5))

For unsorted data, the CHOOSE function can be used to create an array for LOOKUP to search through.

LOOKUP for Last Non-Empty Cell

Finding the Last Non-Empty Cell in a Column: To find the value in the last non-empty cell in a column:

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

This formula uses an array formula technique to find the last non-empty cell in column A.

Additional Notes

LOOKUP requires that the lookup_vector or the first row/column of array be sorted in ascending order for an approximate match.
If lookup_value is smaller than the smallest value in the lookup_vector or array, LOOKUP returns the #N/A error.
LOOKUP is less flexible than VLOOKUP or HLOOKUP and is best suited for straightforward, one-dimensional lookups in sorted data.