Important: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor. This feature is only available if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
The basics of using VLOOKUP.
In the Formula Bar, type = VLOOKUP( ).
In the parentheses, enter your lookup value, followed by a comma. This can be an actual value, or a blank cell that will hold a value: (H2,
Enter your table array or lookup table, the range of data you want to search, and a comma: (H2,B3:F25,
Enter column index number. This is the column where you think the answers are, and it must be to the right of your lookup values: (H2,B3:F25,3,
Enter the range lookup value, either TRUE or FALSE. TRUE finds partial matches, FALSE finds exact matches. Your finished formula looks something like this: =VLOOKUP(H2,B3:F25,3,FALSE)
Want more?
When you need to find information in a large spreadsheet, or you are always looking for the same kind of information, use the VLOOKUP function.
VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone’s name, in order to find out what you don’t know, like their phone number.
So, as an example, I’ll enter part numbers, the thing I know, and find out Prices, the thing I don’t know.
To do that, I’ll click the cell where I want to see the Prices, I’ll enter an = sign, VLOOKUP, and parentheses.
These parentheses will contain a set of arguments, and an argument is just a piece of data that the function needs in order to run.
I’ll enter H2 as the first argument, because that is where I’ll type the part numbers.
Follow that with a comma, and then, I’ll enter the range of cells that contains the data I want to search. That’s this block of data here.
The part numbers start in cell B3, and if I scroll down, you can see the status values end at cell E52.
So, I’ll enter B3, a colon, and E52, then I’ll type another comma.
And you need to do that because the functions won’t work without the colons and commas.
Next, I’ll type the number 3. This tells VLOOKUP that the values I want to see are in the third column from the left in the range of cells I want to search.
In other words, it’s the third column over from the part numbers, the data I know.
Another comma, and I enter FALSE, because that gives me an exact match between part number and price.
And don’t worry, I’ll explain how that works later.
When I press Enter to tell Excel I am done, you can see I get an error message because I haven’t entered a value in cell H2.
But, when I enter a part number, I get a price.
So what just happened? I told Excel, “Here is a value in the left-hand column of my data. Now look through this range of cells, and in the third column to the right, find the value on the same row.”
A lot like a phone book.
VLOOKUP function
Tip: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.
Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
Tip: The secret to VLOOKUP is to organize your data so that the value you look up (Fruit) is to the left of the return value (Amount) you want to find.
Use the VLOOKUP function to look up a value in a table.
The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the table_array argument.
For example, if table-array spans cells B2:D7, then your lookup_value must be in column B.
Lookup_value can be a value or a reference to a cell.
The range of cells in which the VLOOKUP will search for the lookup_value and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references.
The first column in the cell range must contain the lookup_value. The cell range also needs to include the return value you want to find.
The column number (starting with 1 for the left-most column of table_array) that contains the return value.
A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:
How to get started
There are four pieces of information that you will need in order to build the VLOOKUP syntax:
The value you want to look up, also called the lookup value.
The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be TRUE or approximate match.
Now put all of the above together as follows:
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
Examples
Here are a few examples of VLOOKUP:
Example 1
Example 2
Example 3
Example 4
Example 5
Here, columns A-F and H have values or formulas that only use values on the worksheet, and the rest of the columns use VLOOKUP and the values of column A (Client Code) and column B (Attorney) to get data from other tables.
Copy the table that has the common fields onto a new worksheet, and give it a name.
Click Data > Data Tools > Relationships to open the Manage Relationships dialog box.
For each listed relationship, note the following:
The field that links the tables (listed in parentheses in the dialog box). This is the lookup_value for your VLOOKUP formula.
The Related Lookup Table name. This is the table_array in your VLOOKUP formula.
To add a field to the new table, enter your VLOOKUP formula in the first empty column using the information you gathered in step 3.
In our example, column G uses Attorney (the lookup_value) to get the Bill Rate data from the fourth column ( col_index_num = 4) from the Attorneys worksheet table, tblAttorneys (the table_array), with the formula =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
The formula could also use a cell reference and a range reference. In our example, it would be =VLOOKUP(A2,’Attorneys’!A:D,4,FALSE).
Continue adding fields until you have all the fields that you need. If you are trying to prepare a workbook containing data features that use multiple tables, change the data source of the data feature to the new table.
Excel VLOOKUP Function
Introduction
VLOOKUP is probably the most famous function in Excel, for reasons both good and bad. On the good side, VLOOKUP is easy to use and does something very useful. For new users in particular, it is immensely satisfying to watch VLOOKUP scan a table, find a match, and return a correct result. Using VLOOKUP successfully is a rite of passage: from beginner to skilled Excel user.
On the bad side, VLOOKUP is limited and has dangerous defaults. Unlike INDEX and MATCH (or XLOOKUP), VLOOKUP needs a complete table with lookup values in the first column. This makes it hard to use VLOOKUP with multiple criteria. In addition, VLOOKUP’s default matching behavior makes it easy to get incorrect results. Fear not. The key to using VLOOKUP successfully is mastering the basics. Read on for a complete overview.
Arguments
VLOOKUP takes four arguments: lookup_value, table_array, column_index_num, and range_lookup. Lookup_value is the value to look for, and table_array is the range of vertical data to look inside. The first column of table_array must contain the lookup values to search. The column_index_num argument is the column number of the value to retrieve, where the first column of table_array is column 1. Finally, range_lookup controls match behavior. If range_lookup is TRUE, VLOOKUP will perform an approximate match. If range_lookup is FALSE, VLOOKUP will perform an exact match. Important: range_lookup is optional and defaults to TRUE, so VLOOKUP will perform an approximate match by default. See below for more information on matching.
V is for vertical
The purpose of VLOOKUP is to look up information in a table like this:
With the Order number in column B as the lookup_value, VLOOKUP can get the Cust. ID, Amount, Name, and State for any order. For example, to get the name for order 1004, the formula is:
To look up horizontal data, you can use HLOOKUP, INDEX and MATCH, or XLOOKUP.
VLOOKUP is based on column numbers
When you use VLOOKUP, imagine that every column in the table_array is numbered, starting from the left. To get a value from a given column, provide the number for column_index_num. For example, the column index to retrieve the first name below is 2:
By changing only column_index_num, you can look up columns 2, 3, and 4:
Note: normally, we would use an absolute reference for H3 ($H$3) and B4:E13 ($B$4:$E$13) to prevent these from changing when the formula is copied. Above, the references are relative to make them easier to read.
VLOOKUP only looks right
VLOOKUP can only look to the right. In other words, you can only retrieve data to the right of the column that holds lookup values:
To lookup values to the left, see INDEX and MATCH, or XLOOKUP.
Match modes
VLOOKUP has two modes of matching, exact and approximate, controlled by the fourth argument, range_lookup. The word «range» in this case refers to «range of values» – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. A good example of this is using VLOOKUP to calculate grades. When range_lookup is FALSE, VLOOKUP performs an exact match, as in the example above.
Important: range_lookup is optional defaults to TRUE. This means approximate match is the default mode, which can be dangerous. Set range_lookup to FALSE to force exact matching:
Tip: always supply a value for range_lookup as a reminder of expected behavior.
Note: You can also supply zero (0) for an exact match, and 1 for approximate match.
Exact match example
In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:
The formula in H6 to find Year, based on an exact match of movie title, is:
Approximate match example
When you want the best match, not necessarily an exact match, you’ll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in approximate match mode, because in most cases an exact match will never be found. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:
VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will «step back» and match the previous row. This means table_array must be sorted in ascending order by lookup value to use approximate match.
Caution: If range_lookup is omitted or TRUE and table_array is not sorted by the first column in ascending order, VLOOKUP may return incorrect or unexpected results.
First match only
Tip: To retrieve multiple matches in a lookup operation, see the FILTER function.
Wildcard match
The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve information from a table with a partial lookup_value and wildcard. To use wildcards with VLOOKUP, you must use exact match mode by providing FALSE for range_lookup. In the screen below, the formula in H7 retrieves the first name, «Michael», after typing «Aya» into cell H4. Notice the asterisk (*) wildcard is concatenated to the lookup value inside the VLOOKUP formula:
Two-way lookup
Inside the VLOOKUP function, column_index_num is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the needed column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month. The formula in H6 is:
Multiple criteria
The VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP. In the example below, Column B is a helper column that concatenates first and last names together with this formula:
VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:
For details, see this example. For a more advanced, flexible approach, see this example.
Note: INDEX and MATCH and XLOOKUP are better for lookups based on multiple criteria.
VLOOKUP and #N/A errors
If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error means «not found». For example, in the screen below, the lookup value «Toy Story 2» does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:
The #N/A error is useful because tells you something is wrong. The reason for #N/A might be:
To «trap» the NA error and return a different value, you can use the IFNA function like this:
The formula in H6 is:
The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string («») like this:
You can also use the IFERROR function to trap VLOOKUP #N/A errors. However, be careful with IFERROR, because it will catch any error, not just the #N/A error.
VLOOKUP function
Tip: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.
Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
Tip: The secret to VLOOKUP is to organize your data so that the value you look up (Fruit) is to the left of the return value (Amount) you want to find.
Use the VLOOKUP function to look up a value in a table.
The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the table_array argument.
For example, if table-array spans cells B2:D7, then your lookup_value must be in column B.
Lookup_value can be a value or a reference to a cell.
The range of cells in which the VLOOKUP will search for the lookup_value and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references.
The first column in the cell range must contain the lookup_value. The cell range also needs to include the return value you want to find.
The column number (starting with 1 for the left-most column of table_array) that contains the return value.
A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:
How to get started
There are four pieces of information that you will need in order to build the VLOOKUP syntax:
The value you want to look up, also called the lookup value.
The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be TRUE or approximate match.
Now put all of the above together as follows:
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
Examples
Here are a few examples of VLOOKUP:
Example 1
Example 2
Example 3
Example 4
Example 5
Here, columns A-F and H have values or formulas that only use values on the worksheet, and the rest of the columns use VLOOKUP and the values of column A (Client Code) and column B (Attorney) to get data from other tables.
Copy the table that has the common fields onto a new worksheet, and give it a name.
Click Data > Data Tools > Relationships to open the Manage Relationships dialog box.
For each listed relationship, note the following:
The field that links the tables (listed in parentheses in the dialog box). This is the lookup_value for your VLOOKUP formula.
The Related Lookup Table name. This is the table_array in your VLOOKUP formula.
To add a field to the new table, enter your VLOOKUP formula in the first empty column using the information you gathered in step 3.
In our example, column G uses Attorney (the lookup_value) to get the Bill Rate data from the fourth column ( col_index_num = 4) from the Attorneys worksheet table, tblAttorneys (the table_array), with the formula =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
The formula could also use a cell reference and a range reference. In our example, it would be =VLOOKUP(A2,’Attorneys’!A:D,4,FALSE).
Continue adding fields until you have all the fields that you need. If you are trying to prepare a workbook containing data features that use multiple tables, change the data source of the data feature to the new table.
Excel VLOOKUP tutorial for beginners with formula examples
by Svetlana Cheusheva | updated on August 12, 2022
Today we’ll look at how to use VLOOKUP in Excel with many detailed step-by-step examples. You’ll learn how to Vlookup from another sheet and different workbook, search with wildcards, and a lot more.
This article begins a series covering VLOOKUP, one of the most useful Excel functions and at the same time one of the most intricate and least understood. We 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 provide formula examples that cover the most typical usages of VLOOKUP in Excel, and try to make them both informative and fun.
Excel VLOOKUP function
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 a given 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» stands for «vertical» and is used to differentiate VLOOKUP from the HLOOKUP function that looks up a value in a row rather than column (H stands for «horizontal»).
The function is available in all versions of Excel 365 through Excel 2007.
VLOOKUP syntax
The syntax for the VLOOKUP function is as follows:
Basic VLOOKUP formula
Here is an example of the Excel VLOOKUP formula in its simplest form. Please have a look at the below formula and try to «translate» it into English:
=VLOOKUP(«lion», A2:B6, 2, FALSE)
With all the arguments established, you should have no problem reading the whole formula: search for «lion» in A2:A6, find an exact match, and return a value from column B in the same row.
For the sake of convenience, you can type the value of interest in some cell, say E1, replace the «hardcoded» text with the cell reference, and get the formula to look up any value you input in E1:
=VLOOKUP(E1, A2:B6, 2, FALSE)
Does anything remain unclear? Then try looking at it this way:
How to do a Vlookup in Excel
The lookup value in most cases should be a relative reference (like E2) or you can lock only the column coordinate ($E2). When the formula gets copied down the column, the reference will adjust automatically for each row.
To pull the animal names from column B:
To extract speed from column C:
Enter the above formulas in cells F2 and G2, select those cells, and drag the formulas to the below rows:
If you investigate the formula in a lower row, you will notice that the lookup value reference has adjusted for that specific row, while the table array is unchanged:
Below, you will have a few more useful tips that will save you a lot of headache and troubleshooting time.
Excel VLOOKUP examples
I hope vertical lookup is starting to look a bit more familiar to you. To strengthen your knowledge, let’s build a few more VLOOKUP formulas.
How to Vlookup from another sheet in Excel
In practice, the Excel VLOOKUP function is rarely used with data in the same worksheet. Most often you will have to pull matching data from a different worksheet.
To Vlookup from a different Excel sheet, put the worksheet’s name followed by an exclamation mark in the table_array argument before the range reference. For example, to search in the range A2:B10 on Sheet2, use this formula:
=VLOOKUP(«Product1», Sheet2!A2:B10, 2)
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 the mouse.
For instance, this is how you can look up the A2 value in the range A2:A9 on the Prices worksheet and return a matching value from column C:
=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)
How to Vlookup from another workbook in Excel
To Vlookup from a different Excel workbook, put the workbook’s name enclosed in square brackets before the worksheet’s name.
For example, here’s the formula to look up the A2 value on the sheet named Prices in the Price_List.xlsx workbook:
The easiest way to make a VLOOKUP formula that refers to a different workbook is this:
The result will look somewhat like the screenshot below:
Once you close the file with your lookup table, the VLOOKUP formula will continue working, but it will now display the full path for the closed workbook:
How to Vlookup from a named range in another sheet
In case you plan to use the same lookup range in many formulas, you can create a named range for it and type the name directly in the table_array argument.
To create a named range, simply select the cells and type the name you want in the Name box to the left of the Formula bar. For the detailed steps, please see How to name a range in Excel.
For this example, we gave the name Prices_2020 to the data cells (A2:C9) in the lookup sheet and get this compact formula:
=VLOOKUP(A2, Prices_2020, 3, FALSE)
Most names in Excel apply to the entire workbook, so you don’t need to specify the worksheet’s name when using named ranges.
If the named range is in another workbook, put the workbook’s name before the range name, for example:
Such formulas are far more understandable, aren’t they? Besides, using named ranges can be a good alternative to absolute references. Since a named range doesn’t change, you can be sure that your table array will remain locked no matter where the formula is moved or copied.
If you have converted your lookup range into a fully-functional Excel table, then you can do a Vlookup based on the table name, e.g. Price_table in the below formula:
=VLOOKUP(A2, Price_table, 3, FALSE)
Table references, also called structured references, are resilient and immune to many data manipulations. For instance, you can remove or add new rows to your lookup table without worrying about updating the references.
Using wildcards in VLOOKUP formula
Like many other formulas, the Excel VLOOKUP function accepts the following wildcard characters:
Wildcards prove really useful in many situations:
Example 1. Look up text starting or ending with certain characters
Suppose you want to find a certain customer in the below database. You do not remember the surname, but you are confident that it starts with «ack».
To return the last name from column A, use the following Vlookup wildcard formula:
To retrieve the license key from column B, use this one (the difference is only in the column index number):
You can also enter the known part of the name in some cell, say E1, and combine the wildcard character with the cell reference:
The below screenshot shows the results:
Below are a few more VLOOKUP formulas with wildcards.
Find the last name ending with «son»:
Get the name that starts with «joh» and ends with «son»:
Pull a 5-character last name:
Example 2. VLOOKUP wildcard based on cell value
From the previous example, you already know that it is possible to concatenate an ampersand (&) and a cell reference to make a lookup string. To find a value that contains a given character(s) in any position, put an ampersand before and after the cell reference.
Let’s say, you wish to get a name corresponding to a certain license key, but you don’t know the whole key, only a few characters. With the keys in column A, names in column B, and part of the target key in E1, you can do a wildcard Vlookup in this way:
Extract the key:
Extract the name:
VLOOKUP TRUE vs FALSE
And now, it’s time to take a closer look at the last argument of the Excel VLOOKUP function. Though optional, the range_lookup parameter is highly important. Depending on whether you choose TRUE or FALSE, your formula may yield different results.
Excel VLOOKUP exact match (FALSE)
If range_lookup is set to FALSE, a Vlookup formula searches for a value that is exactly equal to the lookup value. If two or more matches are found, the 1st one is returned. If an exact match is not found, the #N/A error occurs.
Excel VLOOKUP approximate match (TRUE)
If range_lookup is set to TRUE or omitted (default), the formula looks up the closest match. More precisely, it searches for an exact match first, and if an exact match is not found, looks for the next largest value that is less than the lookup value.
An approximate match Vlookup works with the following caveats:
The following examples will help you better understand the difference between an exact match and approximate match Vlookup and when each formula is best to be used.
Example 1. How to do an exact match Vlookup
To look up an exact match, just put FALSE in the last argument.
For this example, let’s take the animal speed table, swap the columns, and try to find the animals that can run 80, 50 and 30 miles per hour. With the lookup values in D2, D3 and D4, enter the below formula in E2, and then copy it down to two more cells:
As you can see, the formula returns «Lion» in E3 because they run exactly 50 per hour. For the other two lookup values an exact match is not found, and #N/A errors appear.
Example 2. How to Vlookup for approximate match
To look up an approximate match, there are two essential things you need to do:
Sorting the lookup column is very important because the VLOOKUP function stops searching as soon as it finds a close match smaller than the lookup value. If the data is not sorted properly, you may end up having really strange results or a bunch of #N/A errors.
For our sample data, an approximate match Vlookup formula goes as follows:
And returns the following results:
Special tools to Vlookup in Excel
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 and experience more enjoyable, we included a couple of time-saving tools in our Ultimate Suite for Excel.
The interactive VLOOKUP Wizard will walk you through the configuration options to build a perfect formula for the criteria you specify. Depending on your data structure, it will use the standard VLOOKUP function or an INDEX MATCH formula that can pull values from left.
To get your custom-tailored formula, this is what you need to do:
The following examples show the wizard in action.
Standard Vlookup
When the lookup column (Animal) is the leftmost column in the lookup table, a normal VLOOKUP formula for exact match is inserted:
Vlookup to the left
When the lookup column (Animal) is on the right side of the return column (Speed), the wizard inserts an INDEX MATCH formula to Vlookup right to left:
Extra bonus! Due to the clever use of cells references, the formulas can be copied or moved to any column, without you having to update the references.
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, which works this way:
That’s how to use VLOOKUP in Excel at the basic level. In the next part of our tutorial, we will discuss advanced VLOOKUP examples that will teach you how to Vlookup multiple criteria, return all matches or Nth occurrence, look up across multiple sheets with a single formula, and more. I thank you for reading and hope to see you next week!
