LOOKUP (vector) function

The vector syntax 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. A vector is a range of only one row or one column. Use this form of the LOOKUP function when you want to specify the range that contains the values you want to match.

Syntax

LOOKUP(lookup_value,lookup_vector,result_vector)

Argument

Description

Remarks

lookup_value

A value that LOOKUP searches for in the first vector. The argument lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

• 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.

lookup_vector

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 this argument must be put in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, this function may not give the correct value. Uppercase and lowercase text are equivalent.

result_vector

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

• None.

Example

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.

 1 2 3 4 5 6
 A B Frequency Color 4.14 red 4.19 orange 5.17 yellow 5.77 green 6.39 blue Formula Description (Result) =LOOKUP(4.19,A2:A6,B2:B6) Looks up 4.19 in column A, and returns the value from column B that's in the same row (orange) =LOOKUP(5.50,A2:A6,B2:B6) Looks up 5.50 in column A, matches the next smallest value (5.17), and returns the value from column B that's in the same row (yellow) =LOOKUP(7.66,A2:A6,B2:B6) Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value from column B that's in the same row (blue) =LOOKUP(0,A2:A6,B2:B6) Looks up 0 in column A, and returns an error because 0 is less than the smallest value in the lookup_vector A2:A6 (#N/A)
 Rate this content: