Excel VLOOKUP Function

[swatsp0p]

One of the most useful tools in Excel is the VLOOKUP function. Although easy to use, it can be daunting to the beginner. VLOOKUP simply takes a value you specify (this could be a number, a text string or the result of another formula), finds this value in a table of data you have set up (I always recommend Naming this range) and returns a value in an adjacent column.

From the Help file:
"VLOOKUP: Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column 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 you want to find.
The V in VLOOKUP stands for "Vertical."
Syntax
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])"

>lookup_value is the value you specify to use as the "search for" value in your table

? The value can be numerical, text(enclosed in quotes ""), a cell reference, a range name (not in quotes) or the result of a formula

>table_array is your specified table. This can be a named range (e.g. Table1) or a defined range (e.g. G1:H10)
? The first column MUST be sorted in ASCENDING order. Exel begins at the top and searches for the FIRST value that is equal (an exact match) to your search value. Once it finds a value GREATER than the search value, it stops. (see Range_lookup below for details)

>col_index_num is the "offset" number of columns to return found data from (this must be a POSITIVE number from 1 to the number of columns in your table)
? A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

>range_lookup is an optional entry and is either TRUE or FALSE (default if omitted is TRUE)
? Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the largest value that is less than lookup_value is returned (If no value is less, #N/A is returned). If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. (TRUE can be entered as '1' and FALSE can be entered as '0&#39

EXAMPLE: Say you have a table named 'Table1' in cells G4:H16.
(Note that the Month column is sorted ascending)

Month Avg. Temperature
APR 52
AUG 76
DEC 23
FEB 29
JAN 15
JUL 78
JUN 71
MAR 41
MAY 65
NOV 35
OCT 49
SEP 62

You also have a data entry cell in A4 in which the user will input a value to lookup. You want the output of this lookup to be in cell A1.

In cell A1 you would enter this formula:

=VLOOKUP(A4,TABLE1,2,1) NOTE: notice that the table_array is listed as "Table1" and the Range_lookup is "1". It could also be entered as =VLOOKUP(A4,G4:H16,2,TRUE) or =VLOOKUP(A4,TABLE1,2) with the Range_lookup omitted, defaulting to TRUE.

Now, if in cell A4 the value MAR (or mar) is entered, cell A1 will return 41. If, however, MAB (or mab) is entered, A1 will return 71 (the closest value to MAB that is LESS than MAB is JUN). Had the formula been FALSE, #N/A would be returned, as there is no MAB in the table.

If the table is fairly large or if the worksheet is subject to change (inserting/deleting rows, etc.) I recommend placing the table on a separate worksheet. Using a named range for this table instead of cell references makes it easier to write the formula (less likely to enter incorrect references and no need to name the sheet the reference is pointing to). Another benefit of a named range is that inserting or deleting rows in the middle of the range expands or contracts the named range and doesn't require the user to edit references to that range in all formulas that use it. I often have multiple formulas looking at the same lookup table. Changing the size of this table would require ALL formulas using it to be modified to the new range. Named ranges eliminate this problem.

One last point: the table can also be in a different workbook altogether, however this requires the formula to list the location of the workbook, e.g.:
=VLOOKUP(A4,'C:\Documents and Settings\My Documents\Excel\[Book4.xls]Sheet1'!Table1,2,TRUE). This workbook does not need to be open to work.

So, play around with VLOOKUP and see how it can help you meet the needs of your project, saving you time and effort.

[genesis]

Originally posted by swatsp0p@Jan 26 2005, 08:03 AM
One of the most useful tools in Excel is the VLOOKUP function.* Although easy to use, it can be daunting to the beginner.* VLOOKUP simply takes a value you specify (this could be a number, a text string or the result of another formula), finds this value in a table of data you have set up (I always recommend Naming this range) and returns a value in an adjacent column.

[snapback]2951[/snapback]

This could be really useful to some. Thanks a lot swats.