How can vlookup

If you ever need to apply arbitrary categories to data records, you can easily do so with VLOOKUP, by using a table that acts as the "key" to assign categories. In the example below, we are using VLOOKUP to to calculate a group for each department using a small table named "key" that defines the grouping. In situations where you plan to retrieve information from more than one column in a table, or if you need to copy and paste VLOOKUP, you can save time and aggravation by using absolute references for the lookup value and table array.

This lets you copy the formula, and then change only the column index number to use the same lookup to get a value from a different column. For example, because the lookup value and table array are absolute, we can copy the formula across the columns, then come back and change the column index as needed. Absolute ranges are pretty ugly looking, so can make your VLOOKUP formulas a lot cleaner and easier to read by replacing absolute references with named ranges, which are automatically absolute.

For example, in the employee data example above, you can name the input cell "id" and then name the data in the table "data", you can write your formula as follows:. Not only is this formula easier to read, but it's also more portable, since named ranges are automatically absolute. This is because hard-coded column index values don't change automatically when columns are inserted or deleted. In this example, the lookups for Rank and Sales were broken when a new column was inserted between Year and Rank.

Year continues to work because it is on the left of the inserted column:. If you're getting data from consecutive columns, this trick lets you set up one VLOOKUP formula, then copy it across with no changes required.

For the first formula in cell C3, COLUMN by itself will return 3 because column C is third in the worksheet so we simply need to subtract one, and copy the formula across:. Taking the above tip one step further, you can use MATCH to look up the position of a column in a table and return a fully dynamic column index.

An example would be looking up sales for a salesperson in a particular month, or looking up the price for a particular product from a particular supplier. It may seem counterintuitive, but wildcards let you do an exact match based on a partial match :. If you like, you can adjust the VLOOKUP formula to use a built-in wildcard, like the example below, where we simply concatenate the value in H3 with an asterisk. They give you an easy way to create a "lazy match", but they also make it easy to find the wrong match.

In one way, this is useful because it tell you definitively that there is no match in the lookup table. To trap this error and display a "not found" message instead of the error, you can simply wrap the orignal formula inside of IFERROR and set the result you want:. Here is the formula:. If you are simply retrieving numbers as text from a column in a table, it doesn't matter.

In the following example, the ids for the planet table are numbers entered as text , which causes VLOOKUP to return an error since the lookup value is the number To solve this problem, you need to make sure the lookup value and the first column of the table are both the same data type either both numbers or both text. One way to do this is to convert the values in the lookup column to numbers.

An easy way to do this is to add zero using paste special. If you don't have easy control over the source table, you can also adjust the VLOOKUP formula to convert the lookup value to text by concatenating "" to the value like so:. If you've ever built a series of nested IFs, you know that they work fine, but they require a bit of parentheses wrangling.

You also have to be careful about the order you work in, so as not to introduce a logical error. For example, a common use of nested IFs is to assign grades based on a score of some kind.

Keep in mind your lookup value can be anything: text, numbers, website links, you name it. As long as the value you're looking up matches the value in the referring spreadsheet -- which we'll talk about that in the next step -- this function will return the data you want.

Next to the "table array" field, enter the range of cells you'd like to search and the sheet where these cells are located, using the format shown in the screenshot above. The entry above means the data we're looking for is in a spreadsheet titled "Pages" and can be found anywhere between column B and column K.

The sheet where your data is located must be within your current Excel file. This means your data can either be in a different table of cells somewhere in your current spreadsheet, or in a different spreadsheet linked at the bottom of your workbook, as shown below. For example, if your data is located in "Sheet2" between cells C7 and L18, your table array entry will be "Sheet2!

Beneath the table array field, you'll enter the "column index number" of the table array you're searching through.

For example, if you're focusing on columns B through K notated "B:K" when entered in the "table array" field , but the specific values you want are in column K, you'll enter "10" in the "column index number" field, since column K is the 10th column from the left. In situations like ours, which concerns monthly revenue, you want to find exact matches from the table you're searching through.

This tells Excel you want to find only the exact revenue associated with each sales contact. To answer your burning question: Yes, you can allow Excel to look for an approximate match instead of an exact match.

When VLOOKUP is set for an approximate match, it's looking for data that most closely resembles your lookup value, rather than data that is identical to that value. In order to officially bring in the values you want into your new column from Step 1, click "Done" or "Enter," depending on your version of Excel after filling the "range lookup" field.

This will populate your first cell. You might take this opportunity to look in the other spreadsheet to make sure this was the correct value. If so, populate the rest of the new column with each subsequent value by clicking the first filled cell, then clicking the tiny square that appears on the bottom-right corner of this cell.

All your values should appear. If the syntax is not the problem, how you may have an issue with the values you're trying to receive themselves. For example, if you're looking up URL data, each URL must be a row with its corresponding data to the left of it in the same row.

Keeping with this example, the URLs must match in format in both sheets. Marketers have to analyze data from a variety of sources to get a complete picture of lead generation and more. Editor's note: This post was originally published in March and has been updated for comprehensiveness. Originally published Jul 14, PM, updated July 14 Logo - Full Color.

Contact Sales. Overview of all products. Marketing Hub Marketing automation software. Service Hub Customer service software.

CMS Hub Content management system software. Operations Hub Operations software. Microsoft Excel boasts a huge number of handy functions and utilities, many of which go untouched by the average user.

Short for "vertical lookup," VLOOKUP takes advantage of vertically-aligned tables to quickly locate data associated with a given value. Note: though this tutorial was written for Microsoft Excel , it works the same way in the latest version of Excel. If you know the name of a product, for instance, and you want to quickly determine its price, simply enter the product name into Excel and VLOOKUP will find the price for you.