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, 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

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, 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:

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 orangeLOOKUP(7.66,A2:A6,B2:B6) results in blue

LOOKUP("C",{"a","b","c","d";1,2,3,4}) results in 3LOOKUP("bump",{"a",1;"b",2;"c",3}) results in 2]