This article begins a series covering one of the most useful Excel functions - VLOOKUP. At the same time, it's one of the most intricate and least understood.
In this tutorial, I will try to explain the basics in a very plain language to make the learning curve for an inexperienced user as easy as possible. We will also explore some formula examples that cover the most common usages of VLOOKUP in Excel.
Excel VLOOKUP function - syntax and basic uses
So what is VLOOKUP? To begin with, it is an Excel function : ) What does it do? It searches for the value you specify and returns a matching value from another column. More technically, the VLOOKUP function looks up a value in the first column of the specified range, and returns a value in the same row from another column.
In its common usage, Excel VLOOKUP searches through your data set based on the unique identifier and brings you a piece of information associated with that unique identifier.
The letter "V" in VLOOKUP stands for "vertical". It is used to differentiate VLOOKUP from the HLOOKUP function that looks up a value in the top row of an array (H stands for "horizontal").
The VLOOKUP function is available in all versions of Excel 2016, Excel 2013, Excel 2010, Excel 2007 and lower.
Excel VLOOKUP syntax
The syntax for the VLOOKUP function is as follows:
As you see, the function has 4 parameters, or arguments. The first three parameters are requited, the last one is optional.
- lookup_value is the value to search for.
This can be either a value (number, date or text) or a cell reference (reference to a cell containing a lookup value), or the value returned by some other Excel function. For example:
- Look up for number:
=VLOOKUP(40, A2:B15, 2)- the formula will search for the number 40. - Look up for text:
=VLOOKUP("apples", A2:B15, 2)- the formula will search for the text "apples". Please pay attention that you always enclose text values in "double quotes". - Look up for value in another cell:
=VLOOKUP(C2, A2:B15, 2)- the formula will search for the value in cell C2.
- Look up for number:
- table_array is two or more columns of data.
The VLOOKUP function always searches for the lookup value in the first column of table_array. Your table array may contain various values such as text, dates, numbers, or logical values. Values are case-insensitive, meaning that uppercase and lowercase text are treated as identical.
So, our formula
=VLOOKUP(40, A2:B15,2)will search for "40" in cells A2 to A15 because A is the first column of table_array. Hopefully, everything is clear so far : ) - col_index_num is the column number from which the value should be returned.
The left-most column in the specified table_array is 1, the second column is 2, the third column is 3, and so on.
Well, now you can read the entire formula
=VLOOKUP(40, A2:B15,2). It searches for "40" in cells A2 through A15 and returns a value from column B in the same row (because B is the 2nd column in the specified table array).
- range_lookup determines whether to search for an exact match (FALSE) or approximate match (TRUE or omitted). This parameter is optional but very important. Further on, you will find a few formula examples showing how to Vlookup with exact or approximate match.
Excel VLOOKUP examples
I hope vertical lookup is starting to look a bit more familiar to you. Now, let's build a few VLOOKUP formulas for real data.
How to do vlookup in Excel from another worksheet
In practice, VLOOKUP is rarely used to find data in the same worksheet. Most often you will have to look up and pull the matching data from a different sheet.
To do Vlookup from a different Excel sheet, you should enter the worksheet's name and an exclamation mark in the table_array argument before the range of cells, e.g. =VLOOKUP(40, Sheet2!A2:B15,2). The formula indicates that the lookup range A2:B15 is located in Sheet2.
Of course, you don't have to type the sheet's name manually. Simply, start typing the formula and when it comes to the table_array argument, switch to the lookup worksheet and select the range using a mouse.
The formula you see in the screenshot below searches for the text "Product 1" in column A in the "Prices" worksheet:
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
How to do vlookup from a different workbook
To perform vertical lookup between two different Excel workbooks, you should supply the workbook's name in square brackets before the worksheet's name.
For example, that's how you look up the number 40 in Sheet2 of the Numbers.xlsx workbook:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
If either a workbook name or worksheet name contains spaces or non-alphabetical characters, you should enclose them in 'single quotes' like this:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
The easiest way to make a VLOOKUP formula that refers to another workbook is this:
- Open both workbooks. This step is not obligatory, but makes it easier to write a formula because you won't have to type the workbook's name manually, thus preventing misprints.
- Start typing your formula, and for the table_array argument, switch to the other workbook and select the lookup range there.
In the formula you see in the screenshot below, the lookup workbook PriceList.xlsx and the lookup worksheet is "Prices".
Once you close the workbook with your lookup table, your VLOOKUP formula will work anyway, but it will display the full path for the lookup workbook, as shown below:
For more information, please see How to refer to another Excel sheet or workbook.
How to use a named range or table in VLOOKUP formulas
If you plan to use the same lookup range in several formulas, you can create a named range for it and type the name directly in the table_array argument.
To create a named range, just select the cells and type any name in the Name box, to the left of the Formula bar.
And now you can write the following formula to get the price of "Product 1":
=VLOOKUP("Product 1",Products,2)
Most range names in Excel apply to the entire workbook, so you don't need to specify the worksheet's name, even if your lookup range resides in a different worksheet. If it is in another workbook, put the workbook's name before the named range, for example:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
Such formulas are far more understandable, aren't they? Besides, using named ranges can be a good alternative to absolute cell references. Since a named range doesn't change when a formula is copied to other cells, you can be sure that your lookup range will always remain correct.
If you have converted a range of cells into a fully-functional Excel table (Insert tab > Table), then you can select the lookup range using a mouse, and Microsoft Excel will automatically add the columns' names or the table name to the formula:
The complete formula may look similar to this:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
or even =VLOOKUP("Product 1",Table46,2).
As well as named ranges, columns names are constant and your cell references won't change no matter where the formula is copied within the same workbook.
Using wildcard characters in VLOOKUP formula
Like in many other formulas, you can use the following wildcard characters with Excel VLOOKUP function:
- Question mark (?) to match any single character, and
- Asterisk (*) to match any sequence of characters.
Using wildcard chars may prove really useful in many cases:
- When you do not remember the exact text you are looking for.
- When you want to find some word that is part of the cell's contents. Usually, the VLOOKUP function searches by the entire content of a cell, as if you selected the option "Match entire cell content" in the standard Excel Find dialog.
- When a lookup column contains extra leading or trailing spaces. If it is the case, you may rack your brain trying to figure out why the normal formula does not work.
Example 1. Look up text starting or ending with certain characters
Suppose, you want to find a certain customer in the below database. You cannot remember his surname, but you know it starts with "ack". So, you do a vlookup in this way:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
Once you are sure you've found the correct name, you can use a similar formula to get the sum paid by that customer. You only have to change the 3rd parameter in the formula to the appropriate column index, 3 in our case:
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
Here a few more examples of VLOOKUP formulas with wildcard characters:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE) - find the name ending with "man".
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE) - find the name starting with "ad" and ending with "son".
=VLOOKUP("?????",$A$2:$C$11,1,FALSE) - find a 5-character last name.
Example 2. Wildcard VLOOKUP formulas based on cell's value
And now, let's see how to look up a value in some cell when you know only part of it. Suppose, you have license keys in column A and license names in column B. You want to find a license name corresponding to some license key, of which you have only a few characters. Enter the characters in C1 (lookup value) and use the following formula to pull the License Name:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
Please notice that we use an ampersand (&) before and after a cell reference in the 1st parameter to concatenate a text string.
As you can see in the screenshot below, the VLOOKUP function returns "Jeremy Hill" because his license key contains the group of chars stated in cell C1:
Excel VLOOKUP with exact or approximate match
And finally, let's have a closer look at the last argument of the Excel VLOOKUP function, range_lookup. Though optional, this parameter is highly important because you are likely to get different results depending on whether you supply TRUE or FALSE to the same formula.
For starters, let's see what Microsoft Excel actually means by "exact match" and "approximate match".
Excel Vlookup with exact match
If range_lookup is set to FALSE, the formula searches for a value exactly the same as lookup_value. If there are two or more values that match the lookup value exactly, the 1st value found is returned. If an exact match is not found, the #N/A error is returned.
For example, if you use the formula =VLOOKUP(4, A2:B15,2,FALSE), but value 4 is not found in cells A2 through A15, the formula will return #N/A.
Excel Vlookup with approximate match
If range_lookup is set to TRUE or omitted, the formula will look up the closest match. More precisely, it searches for an exact match first, but if an exact match is not found, it returns the nearest match, which is the next largest value that is less than lookup_value.
To better understand the difference between exact match and approximate match, let's make a few more formulas and see what results they return.
Example 1. How to do a vlookup with exact match
As you remember, to search for exact match, you put FALSE in the last argument. Let's take the Animal speed table from the very first example and find out which animal can run 50 miles per hour. I believe you won't have any difficulties with figuring out the formula:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
Or
=VLOOKUP(E1,$A$2:$B$15,2,FALSE)
Where E1 is the value to look up.
Please note, our lookup range (column A) contains two "50" values in cells A5 and A6; and the formula returns a value from cell B5. Why is that? Because the VLOOKUP function with exact match returns the 1st found value that matches the lookup value.
Example 2. How to Vlookup with approximate match
When using VLOOKUP formulas with approximate match, i.e. with range_lookup set to TRUE or omitted, the first thing you need to do is sort the first column in your table_array in ascending order.
This is very important because your formula will stop searching as soon as it finds the nearest match smaller than lookup_value. If you neglect to sort your data properly, you will end up having really strange results or the #N/A error.
And now you can go with one of the following formulas:
=VLOOKUP(69,$A$2:$B$15,2,TRUE)
=VLOOKUP(E1,$A$2:$B$15,2,TRUE)
=VLOOKUP(E1,$A$2:$B$15,2)
Where E1 is the lookup value.
As an example, let's find the animal whose speed is closest to 69 mph. And here's what our vertical lookup has come up with:
As you see, the formula returns "Antelope" whose speed is 61mph, while we also have Cheetah that runs 70 mile per hour, and 70 is much closer to 69 than 61. So, why does it return "Antelope"? Because VLOOKUP with approximate match retrieves the closest value that is less than the lookup value.
Hopefully, these examples have shed some light on using VLOOKUP in Excel and this function is not alien to you any longer. Now it may be a good idea to revise the essentials to remember the key points better.
Excel VLOOKUP - things to remember!
- The VLOOKUP function cannot look at its left. It always searches for the lookup value in the left-most column of the lookup range (table_array).
- Excel VLOOKUP is case-insensitive, meaning that uppercase and lowercase characters are treated as equivalent.
- If the lookup value is smaller than the smallest value in the first column of table_array, the #N/A error is returend.
- If the 3rd parameter (col_index_num) is less than 1, the #VALUE! error is returend. In case it is greater than the number of columns in table_array, the formula will return the #REF! error.
- Always use absolute cell references (with the $ sign) for the table_array argument to prevent it from changing when copying your formula to other cells.
- When searching with approximate match (range_lookup set to TRUE or omitted), always have the data in the first column of table_array sorted in ascending order.
- And finally, remember about the importance of the last parameter. Supply TRUE for approximate match or FALSE for exact match, and it will save you a lot of headache.
Vlookup Wizard for Excel - easy way to write complex formulas
Undoubtedly, Vlookup is one of the most powerful and useful Excel functions, but it's also one of the most confusing ones. To make the learning curve less steep, embrace the Vlookup Wizard included with of our Ultimate Suite for Excel.
The interactive wizard will walk you through the configuration options to make your own Vlookup formula based on the criteria you specify. Here's how:
- Select any cell in your main table, and click the Vlookup Wizard button on the Ablebits Data tab, in the Merge group.
- The Vlookup Wizard will show up and ask you to choose your main table and lookup table. Additionally, you specify the following 3 columns:
- Key column - the column in your main table containing the values to look up.
- Lookup column - the column to look up against.
- Return column - the column from which to retrieve values.
On a simple dataset, the wizard picks the column automatically based on the column headers. When you hover over a certain box on the right-hand pane, the corresponding element on the left-side diagram gets highlighted so you better understand what the wizard is doing:
- When finished, click the Insert button. This will insert the formula into all cells of your main table:
That was easy, wasn't it?
What else can our wizard do that the standard Excel VLOOKUP cannot? It can look up from right to left!
Left lookup in Excel made easy
As you already know, the Excel VLOOKUP function can only search in the leftmost column of a table. But sometimes you do need to pull values from left. To have it done, use an INDEX MATCH formula that does not care whether your lookup column is on the right or left side of the return column, or have our wizard build such a formula for you automatically :)
Here's an example of a right-to-left lookup in Excel:
Want to give it a try? A free 14-day trial version of the Ultimate Suite can be downloaded here.
Merge Tables Wizard - quick and easy alternative for Excel VLOOKUP
If your Excel files are enormously large and complex, the project's deadline is imminent, and you are looking for someone who can lend you a helping hand, try out the Merge Tables Wizard.
This tool is our visual and stress-free alternative to Excel's VLOOKUP function. You do not have to invest hours to learn how to use it - the smart wizard will walk you through these 4 easy steps:
- Select your main table.
- Select the lookup table.
- Choose one or several common columns as the unique identifier.
- Define whether you want to update values in existing columns or/and add new columns to the end of the table.
Allow the Merge Tables Wizard a few seconds for processing… and here you go:
If this looks like a tool that may be helpful in your work, you are welcome to download a trial version.
If you like any of our tools decide to get a license, don't miss the 15% off coupon code that we've created especially for our blog readers: AB14-BlogSpo
In the next part of our Excel VLOOKUP tutorial we will explore more advanced examples such as performing various calculations with VLOOKUP, extracting values from several columns and more. I thank you for reading and hope to see you next week!
I've solved it, realised should be TRUE instead of FALSE thanks anyway
I knew there had to be a way to look up one cell and then return a neighboring cell, and I found it here. Thank you so much for this simple tutorial, it saved me a ton of time!
I think you are point 7 inverted.
7. And finally, remember about the importance of the last parameter. Supply TRUE for exact match or FALSE for approximate match, and it will save you a lot of headache.
Hi Suhendra,
Good catch, thank you! Fixed.
I can't get my Vlookup to work. I used the same formula last year with no issues. I have excel 2016. I have 2 sheets in one workbook and here is my formula
=VLOOKUP(A2,'Pay Register Total'!A$2:F$1762,6,FALSE)
The formula returns #N/A on every line.
I have the same data, supplier number, in the first column on both sheets.
Please help!!
thanks,
Margaret
Check the format of each column being used in your Vlookup. The #N/A often results due to formatting mismatch.
I am trying to reference another spreadsheet with the name of customers in Column B and needing to reference their spending which is located in column D and divide column D by 4.
Any help you can provide will be greatly appreciated.
I am trying to reference another spreadsheet with the name of customers in Column B and needing to reference their spending which is located in column D and divide column D by 4. There are 700+ customers so I want to ensure that I reference the correct customer with their spending instead of having to key all manually.
Any help you can provide will be greatly appreciated.
Hello, Deb,
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? I kindly ask you to shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
So I need to run a report where I need to count up how many times an item shows up across 12 different sheets (each sheet is a month of data). Is there a way to use vlookup to look in an entire workbook without having to input every worksheet name in the formula?
Hello, Jason,
If you need to count up how many times an item shows up in your sheets, please try the combination of COUNTIF and SUMPRODUCT functions. First you need to create a range containing the names of the relevant sheets, and then use the following formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A14&"'!A2:A10"),C2))
Where &A2:A14& is the list with the sheets names; A2:A10 is the data range that you want to count the specified value across worksheets; C2 is the cell with the value you want to count up.
Hope this will help.
How do I identify records from one worksheet A which is not in worksheet B and then copy only those record from A to B which is not in B.Please help.
Thanks
Your wildcard examples work on no data, but the exact values you used in your example. I tried to replicate your using the cell contents example with the animal table using =VLOOKUP("*"&C1&"*",$A$2:$B$13, 1,FALSE) where C1 has Coy typed into it. It should find "Coyote" and return the speed but it does not. I cannot figure out what is going wrong.
Hello! I have a VLookup that I am using with the IF function to pull pay rates (regular/overtime) and match it with employee names. My data is in 3 columns (Employee,Reg. Rate, OT Rate) and the lookup is checking 2 columns (Employee, Pay Type) for data. It's working great EXCEPT, I cannot get the N/A to hide if the columns are blank. Below is the current formula:
=IF(B8="overtime", VLOOKUP(A8, EmployeeRates, 3, FALSE), VLOOKUP(A8, EmployeeRates, 2, FALSE))
Any help would be greatly appreciated.
=iferror(IF(B8="overtime", VLOOKUP(A8, EmployeeRates, 3, FALSE), VLOOKUP(A8, EmployeeRates, 2, FALSE)),o)
I have to register daily sales details, for example 01-01-2018 Raju sales in rupees Rs.1000/-, Ramu sales in rupees Rs.1500/-, now I need 01-01-2018 individual sales amount in another sheet
You may do with helper column which you will put serial number to make each 01-01-2018 unique then the lookup formular will use those unique ref to pull their respective sales
Big thanks to Microsoft for inventing vlookup function.
As a Senior manager I rely on this, not my staff.
They all leave me. They hate me.
hello
please help me.
If the unique identifiers is repeated a few time in the table for example "Product1" appears three times and I would like the corresponding data or all three times it appears, may I know how it can be done. The three corresponding data is text and should appear on three different lines. Thanks for your help.
Hi Adrian,
The VLOOKUP function cannot return multiple matches, you'd need an array formula for that. A few formula examples can be found here: How to Vlookup multiple matches in Excel.
How would I use vlookup formula to access various files - ie I want the user to enter in the file name into a cell (A1) and the various VLOOKUP formulas would use the A1 text to feed into the external file reference.
example:
=VLOOKUP("Die Size", 'C:\Users\Dwayne\Desktop\[(A1).xlsx] Set-up'!$A$16:$AB$16,13,TRUE)
And in this case the A1 is a cell reference in the same worksheet; and then the user could get the various info without editing every formula.
advice appreciated!
Helpful excel,but can you sheet provide for website.
Thanks
Explain V lookup together to Choose function Any Example?
'v-03'!H33 I have four formulas that are similar but only one doesn't calculate total please help
VLookup expanded here is "Too" Complicated..!!!
Thank you! This is VERY helpful!
need to know how to copy and paste some cells from a page of a spread sheet to an email WITHOUT any formula data included and look exactly as the copied cells
Thank you for such a clear and easy to understand training. Most appreciated.
How i can get the corrosponding value of column B , column C and column D in sheet 1 from sheet 2 with excel formula. Value In column A of both the sheet are matching but not serial wise.