What is the VLOOKUP Function?
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.
VLOOKUP Function Syntax & Arguments
There are four possible parts of 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
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
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
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
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. The XLOOKUP function is similar but works in any direction.
