The VLOOKUP function in Excel is used to find something in a table. If you have rows of data organized by column headings, VLOOKUP can be used to locate a value using the column.

When you do a VLOOKUP, you're telling Excel to first locate the row that contains the data you want to retrieve, and then to return the value that's located in a specific column within that row.

The VLOOKUP function is available in every version of Excel.

## VLOOKUP Function Syntax & Arguments

There are four possible parts to this function:

**=VLOOKUP**(*search_value*, *lookup_table*, *column_number*, [*approximate_match*] )

**search_value**is the value you're searching for. It must be in the first column of*lookup_table*.**lookup_table**is the range you're searching within. This includes*search_value*.**column_number**is the number that represents how many columns into*lookup_table*, from the left, should be the column that VLOOKUP returns the value from.**approximate_match**is optional and can be either*TRUE*or*FALSE*. It determines whether to find an exact match or an approximate match. When omitted, the default is*TRUE*, meaning it will find an approximate match.

## VLOOKUP Function Examples

Here are some examples showing the VLOOKUP function in action:

### Find The Value Next to a Word From a Table

=VLOOKUP("Lemons",A2:B5,2)

This is a simple example of the VLOOKUP function where we need to find how many lemons we have in stock from a list of several items. The range we're looking through is *A2:B5*, and the number we need to pull is in column *2* since "In Stock" is the second column from our range. The result here is *22*.

### Find an Employee's Number Using Their Name

=VLOOKUP(A8,B2:D7,3)

=VLOOKUP(A9,A2:D7,2)

Here are two examples where we write the VLOOKUP function a little differently. They're both using similar data sets but since we're pulling information from two separate columns, *3* and *2*, we make that distinction at the end of the formula—the first one grabs the position of the person in *A8* (Finley) while the second formula returns the name that matches the employee number in *A9* (819868). Since the formulas are referencing cells and not a specific text string, we can leave out the quotes.

### Use an IF Statement With VLOOKUP

=IF(VLOOKUP(A2,Sheet4!A2:B5,2)>10,"No","Yes")

VLOOKUP can also be combined with other Excel functions and use data from other sheets. We're doing both in this example to determine whether we need to order more of the item in Column A. We use the IF function so that if the value in position *2* in *Sheet4!A2:B5 *is greater than *10*, we write *No* to indicate that we don't need to order more.

### Find The Closest Number In a Table

=VLOOKUP(D2,$A$2:$B$6,2)

In this final example, we're using VLOOKUP to locate the discount percentage that should be used for various bulk orders of shoes. The discount we're searching for is in Column D, the range that includes the discount information is *A2:B6*, and within that range is column *2 *that contains the discount. Since VLOOKUP doesn't need to find an exact match, *approximate_match* is left blank to indicate *TRUE*. If an exact match isn't found, the function uses the next smaller amount.

You can see that in the first example of 60 orders, the discount isn't found in the table to the left, so the next smaller amount of 50 is used, which is a 75% discount. Column F is the final price when the discount is figured in.

## VLOOKUP Errors & Rules

Here are some things to remember when using the VLOOKUP function in Excel:

- If
*search_value*is a text string, it must be surrounded in quotes. - Excel will return
*#NO MATCH*if VLOOKUP can't find a result. - Excel will return
*#NO MATCH*if there isn't a number within*lookup_table*that's greater or equal to*search_value*. - Excel will return
*#REF!*if*column_number*is greater than the number of columns in*lookup_table*. *search_value*is always in the far left position of*lookup_table*and is position 1 when determining*column_number*.- If you specify
*FALSE*for*approximate_match*and no exact match is found, VLOOKUP will return*#N/A*. - If you specify
*TRUE*for*approximate_match*and no exact match is found, the next smaller value is returned. - Unsorted tables should use
*FALSE*for*approximate_match*so that the first exact match is returned. - If
*approximate_match*is*TRUE*or omitted, the first column needs to be sorted alphabetically or numerically. If it isn't sorted, Excel might return an unexpected value. - Using absolute cell references lets you autofill formulas without changing
*lookup_table*.

## Other Functions Like VLOOKUP

VLOOKUP performs vertical lookups, meaning that it retrieves information by counting the columns. If the data is organized horizontally and you want to count down the rows to retrieve the value, you can use the HLOOKUP function.

XLOOKUP is similar but works in any direction.