The LOOKUP function returns a value either from a one-row or one-column
range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form
of LOOKUP looks in a one-row or one-column range (known as a vector) for a
value, and then returns a value from the same position in a second one-row or
one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified
value, and then returns a value from the same position in the last row or
column of the array.
The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a
value, and then 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 that you want to match.
Syntax for 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. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE.
If you do not do so, 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.
Note
If LOOKUP cannot 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.
Example
Collapse this tableExpand this table
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
Collapse this tableExpand this table
Formula
Description (Result)
=LOOKUP(4.91,A2:A6,B2:B6)
Looks up 4.19 in column A,
and returns the value from column B that is in the same row (orange).
=LOOKUP(5.00,A2:A6,B2:B6)
Looks up 5.00 in column A,
and returns the value from column B that is in the same row (orange).
=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 is 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:A7 (#N/A).
The array form of LOOKUP looks in the first row or column of an array for the value that
you specify, and then returns a value from the same position in the last row or
column of the array. Use this form of LOOKUP when the values you want to match are in the first row or column
of the array.
Syntax for Array Form
LOOKUP(lookup_value,array)
Lookup_value is a value that LOOKUP searches for in an array. Lookup_value
can be a number, text, a logical value, or a name or reference that refers to a
value.
If LOOKUP cannot find the lookup_value, it uses
the largest value in the array that is less than or equal to
lookup_value.
If lookup_value is smaller
than the smallest value in the first row or column (depending on the array
dimensions), LOOKUP returns the #N/A error value.
Array is a range of cells that
contains text, numbers, or logical values that you want to compare with
lookup_value.
The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first
row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of
array.
If array covers an area that
is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first
row.
If array is square or is
taller than it is wide (more rows than columns), LOOKUP searches in the first column.
With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.
Important The values in array must be placed in
ascending order. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE. If you do
not do so, LOOKUP may not give the correct value. Uppercase and lowercase text are
equivalent.
Example
Collapse this tableExpand this table
A
B
1
a
1
2
b
2
3
c
3
4
d
4
Collapse this tableExpand this table
Formula
Description (Result)
=LOOKUP("c",A1:B4)
Looks up "C" in first row of the
array and returns the value in the last row that is in the same column
(3).
=LOOKUP("bump",A1:B4)
Looks up "bump" in first row of
the array and returns the value in the last column that is in the same row
(2).
Back to the top
