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.