[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
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 |
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, FALSE, TRUE. Upper- and lowercase strings are equivalent. If LOOKUP can't find the lookup-value, it matches the largest value in lookup-vector (or array) that is less than or equal to lookup-value. | |
|
result-vector |
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, FALSE, TRUE. Upper- and lowercase strings are equivalent. If array covers an area that has more columns than rows, lookup-value is searched for in the first row. If array is square or has more rows than columns, lookup-value is searched for in the first column. |
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.
[Example: Given the following data:
|
|
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 orange
LOOKUP(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
end example]