Annotation:
The LOOKUP function scans a cell range for the specified value and then returns another cell located at a matching position (e.g., in the same row, but a different column).
Important: The data to be searched in has to be sorted in ascending order. Tip: There are two alternatives to this function that can also search unsorted data and are more flexible in general: VLOOKUP and HLOOKUP
The LOOKUP function can be used in two different ways:
Usage with two one-dimensional ranges: Here, two vectors (i.e., one-dimensional ranges) are specified for the function: Lookup Vector and ResultVector. If the function finds the search criterion in the Lookup Vector, it returns the cell with the same position in the Result Vector as the result.
Usage with one two-dimensional range: Here, a two-dimensional range is specified for the function. If it finds the search criterion in the first row (or column) of this range, it returns the cell with the same position in the last row (or column) of this range as the result.
Usage with two one-dimensional ranges
Syntax:
LOOKUP(Crit, LookupVector, ResultVector)
Description:
This variant of LOOKUP scans LookupVector for the specified value in Crit. If found, the function returns the content of the cell located in the same row (or column) of the ResultVector.
Crit is the value to be searched for. The search is case insensitive.
Lookup Vector is the cell range or array to search in. This can contain text strings, numbers or logical values. It must be a vector, which is a range of either just one row or one column.
Important: The data in LookupVector must be sorted in ascending order. Otherwise this function might return wrong results. If the values are not sorted, use VLOOKUP or HLOOKUP instead of LOOKUP.
ResultVector is the cell range or array with the values to pick the result from. It also has to be a vector. Additionally, its size and its orientation (horizontal or vertical) must be identical to LookupVector.
Note:
If LOOKUP does not find a value that exactly matches the Crit, the next lower value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the error value #N/A.
Example:
In the following table ...
... LOOKUP returns the following results:
LOOKUP(1, A1:A3, B1:B3) returns Result_1
LOOKUP(2, A1:A3, B1:B3) returns Result_2
LOOKUP(3, A1:A3, B1:B3) returns Result_3
LOOKUP(2.5, A1:A3, B1:B3) returns Result_2 (because it is the next smallest value)
LOOKUP(10, A1:A3, B1:B3) returns Result_3 (because it is the next smallest value)
LOOKUP(0, A1:A3, B1:B3) returns #N/A
See also:
VLOOKUP, HLOOKUP, INDEX, MATCH
Usage with one two-dimensional range
Syntax:
LOOKUP(Crit, Range)
Description:
This variant of LOOKUP scans the first column (or row) of Range for the specified value in Crit. If found, the function returns the content of the cell located at the same position in the last column (or row, respectively) of Range.
Alternatively, this function can also search the range column by column. The search direction is determined by the dimensions of the Range:
If Range contains more columns than rows, the function searches the first row and returns the value from the last row.
If Range contains more rows than columns or is square, the function searches the first column and returns the value from the last column.
Crit is the value to be searched for. The search is case insensitive.
Range is the cell range or array to search in. It must contain in the first row (or column) the values to be searched and in the last row (or column) the results to be returned. These can be text strings, numbers or logical values. The range must be two-dimensional, that is, it must have at least two rows and two columns.
Important: The scanned data in the first row (or column) must be sorted in ascending order. Otherwise this function might return wrong results. If the values are not sorted, use VLOOKUP or HLOOKUP instead of LOOKUP.
Note:
If LOOKUP does not find a value that exactly matches the Crit, the next lower value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the error value #N/A.
Example:
In the following table ...
... LOOKUP returns the following results:
LOOKUP(1, A1:B3) returns Result_1
LOOKUP(2, A1:B3) returns Result_2
LOOKUP(3, A1:B3) returns Result_3
LOOKUP(2.5, A1:B3) returns Result_2 (because it is the next smallest value)
LOOKUP(10, A1:B3) returns Result_3 (because it is the next smallest value)
LOOKUP(0, A1:B3) returns #N/A
See also: