Annotation:
The LOOKUP function searches a cell range for a search criterion (for example, a number or a text string) and then returns the contents of another cell with the corresponding position.
Important: The data to be searched must be available in sorted 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 for which the search is performed. The search is case-insensitive.
LookupVector is the cell range or array to be evaluated. This can contain text strings, numbers or logical values. It must be a vector, which means that LookupVector is a range of either only one row or one column.
Important: The data in LookupVector must be sorted in ascending order. Otherwise, this function may return incorrect results. If the values are not sorted, use VLOOKUP or HLOOKUP instead of LOOKUP.
ResultVector is the cell range or array from which the result of the function is to be returned. It also has to be a vector. In addition, its size and orientation (horizontal or vertical) must be identical to LookupVector.
Note:
If LOOKUP does not find a value that exactly matches Crit, the next smaller value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the #N/A error value.
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 in the same row in the last column of Range.
Alternatively, this function can also search the range column by column. The search direction is determined by the dimensions of 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 for which the search is performed. The search is case-insensitive.
Range is the cell range or array on which the search is to be performed. In the first row (or column), it must contain the values for which the search is performed 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 may return incorrect 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 Crit, the next smaller value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the #N/A error value.
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: