LOOKUP (Vector Form)

The LOOKUP function has two syntax forms: vector and array.

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values you want to match. The other form of LOOKUP automatically looks in the first column or row.

Syntax 1

Vector form

LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value   is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

Lookup_vector   is a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

Important   The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Result_vector   is a range that contains only one row or column. It must be the same size as lookup_vector.

• If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

Examples

In the preceding worksheet:

LOOKUP(4.91,A2:A7,B2:B7) equals "orange"

LOOKUP(5.00,A2:A7,B2:B7) equals "orange"

LOOKUP(7.66,A2:A7,B2:B7) equals "violet"

LOOKUP(7.66E-14,A2:A7,B2:B7) equals #N/A, because 7.66E-14 is less than the smallest value in the lookup_vector A2:A7