Microsoft KB Archive/324986

= How to use the LOOKUP function in Excel =

Article ID: 324986

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q324986



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
 * is a value that LOOKUP searches for in the first vector.  can be a number, text, a logical value, or a name or reference that refers to a value.
 * is a range that contains only one row or one column. The values in  can be text, numbers, or logical values.

Important The values in  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.
 * is a range that contains only one row or column. It must be the same size as.

Note
 * If LOOKUP cannot find the, it matches the largest value in   that is less than or equal to.
 * If  is smaller than the smallest value in , LOOKUP gives the #N/A error value.

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   is a value that LOOKUP searches for in an array. can be a number, text, a logical value, or a name or reference that refers to a value.  If LOOKUP cannot find the, it uses the largest value in the array that is less than or equal to  . If  is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.   is a range of cells that contains text, numbers, or logical values that you want to compare with.

The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for  in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of.  If  covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for   in the first row. If  is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.</li> With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.</li></ul>

Important The values in  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.</li></ul>

Example
Additional query words: XL2007 inf XL2002 XL2003

Keywords: kbfunctions kbhowto KB324986

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.