How to Use the VLOOKUP Function in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

How to Use the VLOOKUP Function in Excel

Data retrieval made simple

By Tim Fisher Tim Fisher Senior Vice President & Group General Manager, Tech & Sustainability Emporia State University Tim Fisher has more than 30 years' of professional technology experience. He's been writing about tech for more than two decades and serves as the VP and General Manager of Lifewire. lifewire's editorial guidelines Updated on January 5, 2021 Reviewed by Jessica Kormos Reviewed by Jessica Kormos Saint Mary-of-the-Woods College Jessica Kormos is a writer and editor with 15 years' experience writing articles, copy, and UX content for Tecca.com, Rosenfeld Media, and many others. lifewire's editorial guidelines Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook The VLOOKUP function in Excel is used to find a value in a spreadsheet. The syntax and arguments are =VLOOKUP(search_value, lookup_table, column_number, [approximate_match] ) This article explains how to use the VLOOKUP function in all versions of Excel, including Excel 2019 and Microsoft 365.

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

=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. The XLOOKUP function is similar but works in any direction. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Find Data with VLOOKUP in Excel How to Use the XLOOKUP Function in Excel How to Search in Google Sheets How to Use the Excel INDEX Function How to Create an Excel Left Lookup Formula Using VLOOKUP INDEX-MATCH vs. VLOOKUP in Excel How to Use the ISBLANK Function in Excel How to Combine the ROUND and SUM Functions in Excel How to Use the Round Function in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function Finding the Location of Data With Excel's MATCH Function How to Use the IF-THEN Function in Excel How to Use Excel's MROUND Function How to Use the COUNTIF Function in Excel How to Limit Rows and Columns in an Excel Worksheet How to Count Data in Selected Cells With Excel's COUNTIF Function Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies