How to use LOOKUP function in Excel?

The `LOOKUP` function in Excel is used to search for a value in a range and return a corresponding value in another range. There are two main forms of the `LOOKUP` function: the “vector form” and the “array form.” Here’s how to use each:

Vector Form

The vector form of the LOOKUP function searches one row or one column for a value and returns a value from the same position in a second row or column.

Syntax:

LOOKUP(lookup_value, lookup_vector, [result_vector])

Parameters:

lookup_value: The value you want to search for in `lookup_vector`.
lookup_vector: A single row or single column range to search.
result_vector: (Optional) A single row or single column range from which to return the result. It must be the same size as `lookup_vector`.

Example:

=LOOKUP(4, A1:A5, B1:B5)

This searches for the value 4 in the range `A1:A5` and returns the corresponding value from the range `B1:B5`.

Array Form

The array form of the LOOKUP function searches for a value in the first row or column of an array and returns a value in the same position from the last row or column of the array.

Syntax:

LOOKUP(lookup_value, array)

Parameters:

lookup_value: The value to search for.
array: A range of cells that contain both the values you want to search and the results you want to return.

Example:

=LOOKUP(4, {1,2,3,4,5; "A","B","C","D","E"})

This searches for the value 4 in the first row of the array and returns the corresponding value (“D”) from the second row of the array.

Important Notes:

Both `lookup_vector` and `result_vector` must be of the same size if specified.
The data in `lookup_vector` should be sorted in ascending order for the LOOKUP function to work correctly.
If an exact match is not found, LOOKUP will return the next smallest value.
If there are multiple matches, it will return the first encountered value.

The `LOOKUP` function can be especially useful for quick searches in relatively small data ranges and is quite useful when newer functions like `VLOOKUP`, `HLOOKUP`, or `INDEX`/`MATCH` combinations are not necessary.

facebooklinkedin