Microsoft KB Archive/324985

= How to use the VLOOKUP function in Excel 2002 and Excel 2003 =

PSS ID Number: 324985

Article Last Modified on 8/30/2004

-

The information in this article applies to:


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002

-



This article was previously published under Q324985



SUMMARY
The VLOOKUP function searches for a value in the leftmost column of a table, and then returns a value in the same row from a column that you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

The V in VLOOKUP stands for &quot;Vertical.&quot;

Syntax for VLOOKUP
VLOOKUP


 * is the value to be found in the first column of the array;  can be a value, a reference, or a text string.
 * is the table of information in which data is looked up. Use a reference to a range or a range name, such as &quot;Database&quot; or &quot;List.&quot;
 * If  is TRUE, you must put the values in the first column of   in ascending order: ... -2, -1, 0, 1, 2 ..., A-Z, FALSE, TRUE. Otherwise VLOOKUP may not return the correct value. If   is FALSE,   does not have to be sorted.
 * You can put the values in ascending order by clicking the Sort command on the Data menu and then selecting Ascending.
 * The values in the first column of  can be text, numbers, or logical values.
 * Text is not case-sensitive.
 * is the column number in  from which the matching value must be returned. A   of 1 returns the value in the first column in  ; a   of 2 returns the value in the second column in , and so on. If   is less than 1, VLOOKUP returns the #VALUE! error value. If   is greater than the number of columns in  , VLOOKUP returns the #REF! error value.
 * is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If the Range_lookup value is TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than  is returned. If the Range_lookup value is FALSE, VLOOKUP will find an exact match. If an exact match is not found, the #N/A error value is returned.

Notes:
 * If VLOOKUP cannot find  and range_lookup is TRUE, VLOOKUP uses the largest value that is less than or equal to.
 * If  is smaller than the smallest value in the first column of , VLOOKUP returns the #N/A error value.
 * If VLOOKUP cannot find  and   is FALSE, VLOOKUP returns the #N/A value.

Example:
Additional query words: inf XL2002 XL2003

Keywords: kbfunctions kbhowto KB324985

Technology: kbExcel2002 kbExcel2002Search kbExcel2003 kbExcel2003Search kbExcelSearch kbExcelWinSearch

-

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

© Microsoft Corporation. All rights reserved.