LOOKUP
Syntax:
vector form: LOOKUP ( lookup-value , lookup-vector , result-vector )
array form: LOOKUP ( lookup-value , array )
Description: The vector form looks in a vector for a value, and returns a value from the same position in a second vector. The array form looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of that array.
Arguments:
Name |
Type |
Description |
---|---|---|
lookup-value |
number, string, logical, name, reference |
The value to search for in lookup-vector (or array). |
lookup-vector |
reference |
A range that contains only one row or one column. The values in lookup-vector can be strings, numbers, or logical values. These values shall be placed in "ascending" order, as follows: …, -2, -1, 0, 1, 2, …, A–Z, |
result-vector |
reference |
A range that contains only one row or column. It shall be the same size as lookup-vector. |
array |
text, number, logical |
A range of cells whose values are to be compared with lookup-value. These values shall be placed in "ascending" order, as follows: …, -2, -1, 0, 1, 2, …, A–Z, |
Return Type and Value: any – The vector form looks in a vector for a value, and returns a value from the same position in a second vector. The array form looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of that array.
However, if
lookup-value is smaller than the smallest value in lookup-vector (or the first row or column of array), the return value is unspecified.
The size of the range specified by result-vector is not the same as that specified by lookup-vector, the return value is unspecified.
The values in lookup-vector (or array) are not in "ascending" order, the return value is unspecified.
A |
B |
|
---|---|---|
1 |
Frequency |
Color |
2 |
4.14 |
red |
3 |
4.19 |
orange |
4 |
5.17 |
yellow |
5 |
5.77 |
green |
6 |
6.39 |
blue |
LOOKUP(4.19,A2:A6,B2:B6) results in orangeLOOKUP(5,A2:A6,B2:B6) results in orange
LOOKUP(7.66,A2:A6,B2:B6) results in blue
LOOKUP("C",{"a","b","c","d";1,2,3,4})
results in 3
LOOKUP("bump",{"a",1;"b",2;"c",3})
results in 2
]