![2011 2011](/uploads/1/2/5/5/125586373/503399961.jpg)
Use the VLOOKUP function to look up a value in a table. Syntax VLOOKUP (lookupvalue, tablearray, colindexnum, rangelookup) For example:. =VLOOKUP(105,A2:C7,2,TRUE). =VLOOKUP('Fontana',B2:E7,2,FALSE) Argument name Description lookupvalue (required) 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 table-array.
Mac shortcut. Note: in Excel 2016 for the Mac, you can also use fn + F4. VLOOKUP is a great way to merge data in two tables based on a common ID.
For example, if table-array spans cells B2:D7, then your lookupvalue must be in column B. See the graphic below. Lookupvalue can be a value or a reference to a cell. Tablearray (required) The range of cells in which the VLOOKUP will search for the lookupvalue and the return value. The first column in the cell range must contain the lookupvalue (for example, Last Name in the picture below.) The cell range also needs to include the return value (for example, First Name in the graphic below) you want to find. Learn how to.
Colindexnum (required) The column number (starting with 1 for the left-most column of table-array) that contains the return value. Rangelookup (optional) A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:. TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one. FALSE searches for the exact value in the first column. 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, optionally specify TRUE for approximate match or FALSE for an exact match).
Examples Here are a few examples of VLOOKUP: Example 1 Example 2 Example 3 Example 4 Example 5. Problem What went wrong Wrong value returned If rangelookup is TRUE or left out, the first column needs to be sorted alphabetically or numerically. If the first column isn't sorted, the return value might be something you don't expect. Either sort the first column, or use FALSE for an exact match. #N/A in cell. If rangelookup is TRUE, then if the value in the lookupvalue is smaller than the smallest value in the first column of the tablearray, you'll get the #N/A error value.
If rangelookup is FALSE, the #N/A error value indicates that the exact number isn't found. For more information on resolving #N/A errors in VLOOKUP, see. In cell If colindexnum is greater than the number of columns in table-array, you'll get the #REF! For more information on resolving #REF! Errors in VLOOKUP, see. In cell If the tablearray is less than 1, you'll get the #VALUE! For more information on resolving #VALUE!
Errors in VLOOKUP, see. In cell The #NAME? Error value usually means that the formula is missing quotes. To look up a person's name, make sure you use quotes around the name in the formula.
For example, enter the name as 'Fontana' in =VLOOKUP('Fontana',B2:E7,2,FALSE). For more information, see. Do this Why Use absolute references for rangelookup Using absolute references allows you to fill-down a formula so that it always looks at the same exact lookup range. Learn how to use. Don't store number or date values as text.
When searching number or date values, be sure the data in the first column of tablearray isn't stored as text values. Otherwise, VLOOKUP might return an incorrect or unexpected value.
Sort the first column Sort the first column of the tablearray before using VLOOKUP when rangelookup is TRUE. Use wildcard characters If rangelookup is FALSE and lookupvalue is text, you can use the wildcard characters—the question mark (?) and asterisk (.)—in lookupvalue.
A question mark matches any single character. An asterisk matches any sequence of characters.
If you want to find an actual question mark or asterisk, type a tilde in front of the character. For example, =VLOOKUP('Fontan?' ,B2:E7,2,FALSE) will search for all instances of Fontana with a last letter that could vary. Make sure your data doesn't contain erroneous characters. When searching text values in the first column, make sure the data in the first column doesn't have leading spaces, trailing spaces, inconsistent use of straight ( ' or ' ) and curly ( ‘ or “) quotation marks, or nonprinting characters.
In these cases, VLOOKUP might return an unexpected value. To get accurate results, try using the or the to remove trailing spaces after table values in a cell. Need more help? You can always ask an expert in the, get support in the, or suggest a new feature or improvement on.
Excel is a pretty amazing piece of software. On the surface it looks like a basic spreadsheet application, but just below this is a wealth of tools that can make even the most complex and time-consuming jobs easy. Take, for example, the VLOOKUP function. This amazing little tool can take seemingly meaningless data like model numbers or employee IDs and magically turn it into information we can read, such as names and email addresses.
Instead of copying and pasting the correct email address next to every name in a list, VLOOKUP can automatically assign the right email address to the right person. It doesn’t matter if your list has 10 entries or 10,000 – VLOOKUP can make it work. Read on to see an example of VLOOKUP in action and find out how you can use it.
All you need to use VLOOKUP is a set of data to reference and a list that needs to reference that data. If you want to see the example in action, just download the example file used below by clicking. Note: The example below shows you the manual method for creating a VLOOKUP and is shown with Office for Mac. This works the same for Windows, and while there are tools to build this inside of Excel, it’s far quicker to understand how it works and build these functions manually. RELATED: About VLOOKUP VLOOKUP is an Excel function that uses a referential table of data to apply necessary information to another place. This data can be another table in the workbook you’re using, another table on a worksheet, or in another Excel file altogether. This means if you use Employee numbers, for example, you can have an employee Excel file and reference data from it inside of countless other Excel files, as long as they’re in the same folder.
So why is this useful? Let’s say you’re doing payroll. Most payroll applications only care about Employee ID or Social Security number, as each person only has one of these. But there can be multiple people with the same name.
So, when you get your report it only has ID numbers on it. To make it readable you’d want to add names and possibly even departments. Instead of typing it by hand, you can reference it from a spreadsheet automatically. This is best shown with an example, so let’s take a look at using VLOOKUP for employees. How to Use VLOOKUP The first thing you need is your source data.
This is the sheet that has both the hard-to-read information like an employee ID as well as the easy-to-read information like Employee Name. We’ll call this the Lookup Table. The VLOOKUP function uses four key pieces of information. This may seem imposing, but it’s actually pretty simple. For the sake of this tutorial we’ll do all of our work in one spreadsheet, but you can do this across Excel files in the same folder structure, too. Using the file shown above, we’re going to make a new page named “Working” and rename Sheet1 to “Lookup.” We’ll pretend we have timesheet data that needs names and departments attached to it. This means there will be multiple entries for the same person.
Here’s the data we’re starting with: The highlighted area is what we would like to fill in automatically with VLOOKUP. By referencing the data in the Lookup sheet, we’ll do this easily. The arguments VLOOKUP needs are:. lookupvalue.
tablearray. colindexnum. rangelookup You can start the VLOOKUP function by typing =VLOOKUP in the cell you want data to show up in. For our example above, this would be cell D2. Excel will start to auto-format your formula, but just keep typing, as we’ll show you next what goes in here.
Duplicate contacts killing your productivity? See how Scrubly can help in this. Lookupvalue – This is the first part of the argument. This is the information you will be using for reference. In our example this is the Employee ID field. After typing =VLOOKUP into cell D2, our next step is to click on the employee ID in cell B2. This tells Excel that the employee ID we want referenced is there.
Tablearray – This argument refers to the range of data you want Excel to check. For our example, we need to click on the Lookup tab and then highlight the area we’re referencing. There are a couple of key rules that need to be followed here. References to the Left – The left column must have the values being referenced.
You’ll notice in our example at the top of this page that Employee ID is listed in the first column of the Lookup tab. In other words, you couldn’t place the name columns first. No Duplicates – The column you’re using for reference can’t contain duplicates. Since this shows a list of employees, you should never have two employees with the same ID. Colindexnum – This argument refers to the column number on your lookup table that has the information you want displayed. Looking at our example, we want the employee name, which is stored in the fourth column on the lookup sheet. If you just wanted the last name, you’d want column #3.
Range-lookup – The last field asks if you want a close match or an exact match. For this example we do indeed want an exact match, so the value we’ll enter is FALSE. More often than not, you’ll want to use FALSE here. So, here’s what the formula looks like for our example: =VLOOKUP(B2,Lookup!$A$2:$E$8,4,FALSE) Important: You probably noticed the additional dollar signs “$” in the example above. These are used to tell Excel that the values are absolute. If you don’t use them, your VLOOKUP will get wonky really fast. Don’t worry too much about them and just add them in.
You’ll be glad you did. Excel uses the Employee ID on the Working sheet to match up on the Lookup sheet and grabs whatever data we have in the fourth column. In our case that’s the Employee Name. The result is that the employee’s name shows up. To copy it to the rest of the cells in your table, just hover over the bottom right corner of the cell you just worked in and your cursor should turn into a small black plus sign.
Click and drag down to all the cells you want filled. When you release, all the names should fill in automatically. To fill the Department column you would use the same formula, but this time you would reference column #5 for your colindexnum argument. When you’re done, your sheet should look like this: There are countless reasons to use VLOOKUP, from data validation to making sheets that a human can easily read.
Anytime you need to fill in data en masse, VLOOKUP is your best friend. If you’d like to learn more about VLOOKUP, check out on the topic.