DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 324986 - Last Review: September 30, 2013 - Revision: 7.0

This article was previously published under Q324986

On This Page

Summary

This article describes how to use the LOOKUP function in a Microsoft Excel worksheet.

More information

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.

Vector Form of LOOKUP

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
AB
1FrequencyColor
24.14red
34.19orange
45.17yellow
55.77green
66.39blue
Collapse this tableExpand this table
FormulaDescription (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).

Array Form of LOOKUP

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
AB
1a1
2b2
3c3
4d4
Collapse this tableExpand this table
FormulaDescription (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).
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.

Applies to
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
Keywords: 
kbfunctions kbhowto KB324986
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support