VLOOKUP function in Excel looks for a value in a range of cells, then it returns a value that’s in the same row as the value you’re looking for.
VLOOKUP which stands for ‘Vertical Lookup’, is a search function that searches for a value in the leftmost column (first column) and returns the corresponding value from a column to the right. VLOOKUP only looks up (top to bottom) data in a table organized vertically.
For example, Let’s say, you have an inventory list in a worksheet with a table showing item names, date of purchase, quantity, and price. Then, you could use VLOOKUP in a separate worksheet to pull the quantity and price for a certain item name from the first worksheet.
VLOOKUP function may look daunting at first, but it is actually quite easy to use once you understand how it works. Here, in this article, we’ll show you how to use the VLOOKUP function in Excel.
VLOOKUP Syntax and Arguments
If you’re going to use the VLOOKUP function, you need to know its syntax and its arguments.
Syntax of VLOOKUP function:
A VLOOKUP function consists of 4 parameters or arguments:
- lookup_value: This is the value that you are looking for in the first column of the table array. The Lookup value must be in the left-most column of your table.
- table_array: This is the table (range of cells) in which you want to look up/search. This table can be in the same worksheet or another worksheet, or even another workbook from the one in which you enter the VLOOKUP function.
- col_index_num: This is the column number of the Table Array that has the value you want to fetch.
- [range_lookup]: This parameter asks you if you want an exact match or approximate match. Enter FALSE if you want to search for the exact value or enter TRUE if you’re OK with the approximate value.
Using VLOOKUP function in Excel
Let’s explore how to use VLOOKUP in Microsoft Excel.
To use VLOOKUP, first, you need to create your database or table (see below).
Then create a second table where you want to look up the values from the first table.
Next, select the cell where you want to enter the looked-up value and enter the VLOOKUP function. For example, we want to lookup the Phone number of ‘Ena’, then we have to enter the lookup value as B13, A2:E10 as the table array, 5 for the column number of the phone number, and FALSE to return the exact value. Then, press ‘Enter’ to finish the formula.
Of course, you don’t have to type the table range manually. Simply, start typing the formula and when it comes to the table_array argument, select the range using the mouse. And it will be added to the argument automatically.
Remember, the Lookup-value must be in the first column of our search range (A2:E10). Also, the lookup value doesn’t have to be in column A of your spreadsheet, just the first column of your search range.
Vlookup Looks Right
The VLOOKUP function can only look to the right. It searches for a value in the leftmost column of a table and returns the corresponding value from a column to the right.
VLOOKUP has two modes of matching, exact and approximate. The ‘range_lookup’ parameter determines whether you are looking for an exact match or an approximate match.
If range_lookup is set to ‘FALSE’ or ‘0’, the function searches for a value that is exactly equal to the lookup value.
If an exact match is not found in the table, it will return a #N/A error. When we tried to look up ‘Japan’ and return its corresponding value in column 4, the #N/A error occurs because there is no ‘Japan’ in the first column of the table.
You can also add the number ‘0’ in the final argument instead of ‘FALSE’. They both mean the same thing in Excel.
Sometimes you don’t necessarily need an exact match, the best match is enough. In such cases, you can use approximate match mode. Set final argument to ‘TRUE’ to find an approximate match. range_lookup default value is also TRUE, which means if you don’t add the last argument, VLOOKUP will use approximate matching by default.
In this example, we don’t need an exact score to find an appropriate grade. All we need is the marks to be in that score range.
If VLOOKUP finds an exact match, then it will return that value. In the above example, if the VLOOKUP function cannot find the value 89 in the first column, then it will return the largest value smaller than 89.
If the leftmost column of the table contains duplicates, the VLOOKUP will find the first match when the match mode is exact.
For example, VLOOKUP is configured to find the last name for the first name ‘Mia’. Since there are 2 entries with the first name ‘Mia’, so the function returns the last name for the first entry, ‘Bena’.
The VLOOKUP function allows you to perform a partial match on a lookup value using wildcards. To find a value that contains a lookup value in any position, put an ampersand before and after the cell reference. Use ‘$’ signs to make absolute cell references and add wildcard ‘*’ sign before or after the lookup value.
In the example, we only have part of a lookup value (Vin) in cell B13. So, in order to perform a partial match on the given characters concatenate a wildcard ‘*’ after the cell reference.
VLOOKUP function allows you to create a dynamic two-way lookup, matching on both rows and columns. In the picture below, VLOOKUP is configured to perform a lookup based on First Name (Mayra) and City. The syntax in B14 is:
How to VLOOKUP from another sheet in Excel
Usually, the VLOOKUP function is used to pull matching data from a different worksheet and it is rarely used with data in the same worksheet.
To Vlookup from another Excel sheet, put the worksheet’s name followed by an exclamation mark, followed by the range reference in the table_array argument.
For example, this is how you can look up the A2 value from the ‘Products’ worksheet in the range A2:B8 on the ‘ItemPrices’ worksheet and return a matching value from column B:
The picture below shows a table in the ‘ItemPrices’ worksheet.
When we enter the VLOOKUP function in column C of the ‘Products’ worksheet, it pulls matching data from the ‘ItemPrices’ worksheet.
Instead of manually tying the sheet’s name, you can simply, start typing the formula and when it comes to the table_array argument, switch to the lookup worksheet and select the range using the mouse.
How to VLOOKUP from Another Workbook in Excel
You can also lookup value on an entirely different workbook. To VLOOKUP from another workbook, put the workbook’s name enclosed in square brackets before the worksheet’s name in the table_array argument.
For example, here’s the formula to look up the A2 value on the sheet named ‘ItemPrices’ in the ‘Item.xlsx’ workbook:
Open both workbooks. Start typing the formula and when it comes to the table_array argument, switch to the other workbook and select the range using the mouse. This way you don’t have to type the workbook and worksheet name manually. type the rest of the arguments and press ‘Enter’ to finish the function.
Even if you close the workbook that contains the lookup table, the VLOOKUP formula will continue working, but it will now display the full path for the closed workbook as its shown in the following screenshot.
Use VLOOKUP Function from Excel Ribbon
If you can’t remember formulas, you can always access the VLOOKUP function from Excel Ribbon. To do that, go to the ‘Formulas’ tab in Excel and click the ‘Lookup & Reference’ icon. Then, select the ‘VLOOKUP’ option at the bottom of the drop-down.
Then, enter arguments in the ‘Function Arguments’ dialog box. Then, click ‘OK’ button.
In the example, we searched for the first name ‘Sherill’ in the table to return its corresponding state in column D.
We hope you learned how to use the VLOOKUP function in Excel from this article. If you want to know more about how to use Excel, check out our other Excel-related articles.