The
VLOOKUP function on
Microsoft Excel allows you to search a value in the first column of data and return the contents of another column.
Microsoft Excel: VLOOKUP Function
The basic
syntax of the VLOOKUP function looks like this:
VLookup(value, table_array,index_number,matching_preferences)
.
For this tutorial, we will use the data in the following charts. Column A shows you
Codes; Column B shows the
Code Descriptions, Column C the
Quantities, and Column D the
Price:
Using VLOOKUP (Excel 2003 and Excel 2007)
References
You must select the references in your table. In this example your reference is
$A$2:$D$10. Once you made your selection click Insert > Name > Define=
- Excel 2007: The definition of the name is done via the Formula menu.
Target cells- where should data be generated?
In this example:
H1 = search term
H4 = Description found (column2 of our table)
H6 = Amount found (column 3 of our table)
H8 = Price found (column 4 of our table)
Formulas
The basic syntax of the VLOOKUP function is:
=VLOOKUP(value_search,table_array,no_index_col,range_lookup)
- H4
- To avoid message #N/A if nothing is found, use the following formula and "Unknown" will appear in the cell.
=IF(ISNA(VLOOKUP($H$1,CHAMPS,2,FALSE)),"Unknown",VLOOKUP($H$1,CHAMPS,2,FALSE))
- Excel 2007 the formula can be shortened:
=IFERROR(VLOOKUP($H$1,CHAMPS,2,FALSE),"Unknown")
- H6
=VLOOKUP($H$1,CHAMPS,3,FALSE)
- To avoid message #N/A if nothing is found, use the following formula and "Unknown" will appear in the cell.
=IF(ISNA(VLOOKUP($H$1,CHAMPS,3,FALSE)),"Unknown",VLOOKUP($H$1,CHAMPS,3,FALSE))
- H8
- To avoid message #N/A if nothing is found, use the following formula and "Unknown" will appear in the cell.
=IF(ISNA(VLOOKUP($H$1,CHAMPS,4,FALSE)),"Unknown",VLOOKUP($H$1,CHAMPS,4,FALSE))
- Excel 2007 the formula can be shortened:
=IFERROR(VLOOKUP($H$1,CHAMPS,2,FALSE),"Unknown")
Note: In the formula put the parameter as FALSE if you want to find the exact value (#N/A if not found), and set it to TRUE if you want the value less than or equal to your search, and in this case the list of values ??must be sorted.
Published by
aakai1056.
Latest update on February 18, 2016 at 05:01 AM by ChristinaCCM.