VLOOKUP which stands for ‘Vertical Lookup’, is a search function that searches for a value in the leftmost column (first column) of the range and returns the parallel value from the column to its right. VLOOKUP function only looks up (top to bottom) value in a table arranged vertically.
For example, let’s say, we have an inventory list in a worksheet with a table showing item names, date of purchase, quantity, and price. Then, we could use VLOOKUP in another worksheet to extract the quantity and price for a certain item name from the inventory 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 tutorial, 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:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])This function consists of 4 parameters or arguments:
- lookup_value: This specifies the value that you are searching for in the first column of the given table array. The Lookup value must always be in the left-most (column of the search table.
- table_array: This is the table (range of cells) in which you want to look up a value. This table (search table) can be in the same worksheet or different worksheet, or even a different workbook.
- col_index_num: This specifies the column number of the table array that has the value you wish to extract.
- [range_lookup]: This parameter specifies if you want to extract an exact match or approximate match. It’s either TRUE or FALSE, enter ‘FALSE’ if you want 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.
Basic Example
To use VLOOKUP, first, you need to create your database or table (see below).
Then create a table or range from where you want to look up and extract the values from the search table.
Next, select the cell where you want the extracted value and enter the following VLOOKUP formula. 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.
=VLOOKUP(B13,A2:E10,5,FALSE)You do not need to type the table range manually, you can just select the range or table using the mouse for the table_array argument. And it will be added to the argument automatically.
Remember, for this to work, the Lookup-value must be in the left-most of our search table (A2:E10). Also, the Lookup_value does not necessarily have to be in column A of the worksheet, it just has to be the left-most column of the range you want to search.
Vlookup Looks Right
The VLOOKUP function can only look to the right of the table. It looks for a value in the first column of a table or a range and extracts the matching value from a column to the right.
Exact Matching
Excel VLOOKUP function has two methods of matching, they are: exact and approximate. The ‘range_lookup’ parameter in the VLOOKUP function specifies what kind of you are looking for, exact or approximate.
If you enter range_lookup as ‘FALSE’ or ‘0’, the formula looks for a value that is exactly equal to the lookup_value (it can be a number, text, or date).
=VLOOKUP(A9,A2:D5,3,FALSE)
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 enter either number ‘0’ or ‘FALSE’ in the final argument. They both mean the same thing in Excel.
Approximate Matching
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 of the function to ‘TRUE’ to find an approximate match. The default value is TRUE, which means if you don’t add the last argument, the function will use approximate matching by default.
=VLOOKUP(B10,A2:B7,2,TRUE)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 formula can’t find the look_up value 89 in the first column, then it will return the next largest value (80).
First Match
If the leftmost column of the table contains duplicates, the VLOOKUP will find and return the first match.
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’.
Wildcard Match
The VLOOKUP function allows you to find a partial match on a specified value using wildcard characters. If you want to locate a value that contains the lookup value in any position, add an ampersand sign (&) to join our lookup value with the wildcard character (*). 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($B$13&"*",$A$2:$E$10,3,FALSE)
Multiple lookups
VLOOKUP function allows you to create a dynamic two-way lookup, matching on both rows and columns. In the following example, VLOOKUP is set up to perform a lookup based on First Name (Mayra) and City. The syntax in B14 is:
=VLOOKUP(B13,A2:E10,MATCH(A14,A1:E1,0),0)
How to VLOOKUP from another sheet in Excel
Usually, the VLOOKUP function is used to return matching values from a separate worksheet and it is rarely used with data in the same worksheet.
To Vlookup from another Excel sheet but in the same workbook, enter the sheet’s name before table_array with an exclamation mark (!).
For example, to look up the cell A2 value of the ‘Products’ worksheet in the range A2:B8 on the ‘ItemPrices’ worksheet and return a corresponding value from column B:
=VLOOKUP(A2,ItemPrices!$A$2:$C$8,2,FALSE)The picture below shows a table in the ‘ItemPrices’ worksheet.
When we enter the VLOOKUP formula in column C of the ‘Products’ worksheet, it pulls matching data from the ‘ItemPrices’ worksheet.
How to VLOOKUP from Another Workbook in Excel
You can also lookup the value on an entirely different workbook. If you want to VLOOKUP from another workbook, you need to enclose the workbook’s name in square brackets followed by the sheet’s name before table_array with an exclamation mark (!) (as shown below).
For example, use this formula to look up the cell A2 value of a different worksheet from the worksheet named ‘ItemPrices’ in the ‘Item.xlsx’ workbook:
=VLOOKUP(A2,[Item.xls]ItemPrices!$A$2:$B$8,2,FALSE)First, open both workbooks, then start entering the formula on cell C2 of a worksheet (Product worksheet), and when you get to the table_array argument, go to the main data workbook (Item.xlsx) and select the table range. This way you do not have to type the workbook and worksheet name manually. Type the rest of the arguments and hit the ‘Enter’ key to finish the function.
Even if you close the workbook that contains the lookup table, the VLOOKUP formula will continue to work, but you can now see the full path of the closed workbook as 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 access VLOOKUP, go to the ‘Formulas’ tab in Excel Ribbon 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 the ‘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.
Member discussion