Technical Support: How to use the Vlookup function in Exel

The VLOOKUP function is a handy one to know when you want Excel to lookup a value in one place and insert it in another. For example, let’s say you have a list of all of your customers on a sheet named “Accounts” and an invoice on another sheet named “Invoice”. When you type in their account number on the Invoice, you want Excel to fill in the name of the customer and their address (and this information is included for all customers on the Accounts sheet). A VLOOKUP will do this for you.
Make a small sample workbook to try this out. Name Sheet One “Invoice” and name Sheet Two “Accounts”. On the Accounts sheet, put three columns of data. Column A would be Account Numbers, Column B would be Customer Name, and Column C would be Address. Add at least five pretend customers, so you have enough to play with. On the Invoice sheet, just add these five column headings in cells A1:E1 – Date, Product Ordered, Account #, Customer Name, Address -but don’t put any data in there yet. (In reality, this sheet would be an actual invoice which included sections for you to add ordering info for any products they buy, etc. But for this example, let’s keep it simple.)
Now, before we try the VLOOKUP, the best thing to do is name the range of data that includes the info you want to pull over from the Accounts sheet. You can do VLOOKUPs without naming the range, but then you MUST be sure to use absolute cell references. So, I find naming the range a much easier way to do it. Highlight all the data on the Accounts sheet and name it Customers (don’t include the column headings in the named range – just the data). If you don’t know how to name ranges, read this TechTrax article I wrote on how to do that.
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=281
Now that you have your data and have named the range, let’s look at building a simple VLOOKUP formula. Assume we will be typing account numbers into cell C2 and wanting the customer’s name and address to be filled into D2 and E2.
The best way to learn new formulas is to use the Insert Function button . In Excel 97 and 2000, it's a button on your Standard Toolbar. In Excel 2002/2003, it's on your Formula Bar. On the Invoice sheet, click into cell D2 and click on the fx button.

(The picture above shows how this box looks in Excel 97/2000. In 2002/2003 it’s slightly different, but I think you can figure it out.)
If you know what type of function you are looking for, you can select the category and all the functions within that category will be listed. However, if you don't know what category you need, you can select "All" in the category list and all of Excel's functions will be listed. Notice as you click on any function name, Excel displays a description of what that function does below the boxes. For this exercise, select the "Lookup & Reference" category and scroll down to select the VLOOKUP function. Note that it tells you that this function “searches for a value in the leftmost column of a table and returns a value from the same row in that table based on what column in that row you specify.” Click OK.
Once you click OK, you will get the wizard which helps you with your VLOOKUP formula. Now, because we will be typing an Account # in cell C2, that is the value we must put in the first box of this wizard which will tell Excel to look for whatever is in C2 in the leftmost column of our lookup table (which we created on the Accounts sheet and named "Customers"). So, enter C2 into the top box.
Click into the next box where it says "Table_array". Notice at the bottom of this box, it tells you what each box you click inside needs. This is where we need to identify our table so Excel knows where to look. So, in this box, simply type Customers. (If you didn’t name the range, you will have to put the absolute reference including the sheet name here … this is why it’s easier to just name the range.)
Click into the third box. This one wants to know the number of the column we want returned. Remember that what you entered in the first box in this wizard must ALWAYS be in the first column of your lookup table. So, in our table, the Account # is in the first column and the Customer Name is in the second column. Since the customer name is what we want to put here, just type a 2 to let Excel know we want what is in the second column.
Notice the last box is labeled "Range_lookup" and it is the only label that is not bold. Whenever a label in this wizard is not bold, that means this "argument" of the function is not required. However, if you do not enter anything in this box, Excel will apply the default. If you read the instructions at the bottom of this box, you will see that the default for this box is "true" which will find the "closest match", whereas "false" will find an "exact match". Since we want an exact match, type false in this box. This is what it should look like if you have entered all the info correctly:

Click OK and you will see that cell D2 now shows #N/A, which simply means that there is no value yet in C2, so the information is "not available". Look in your formula bar and you will see the formula is =VLOOKUP(C2,Customers,2,FALSE). As you get more used to using functions, you won't have to use the wizard as much if you take the time to look at the formulas and start to understand how they work.
Now click into cell E2 and add a VLOOKUP formula which will find the Address in our table. The formula will be exactly the same, except the "Col_index_num" will be 3 instead of 2 because we want to return the address, which is in the third column of our table. Once you have added this, you should see another #N/A in cell E2. The formula will be =VLOOKUP(C2,Customers,3,FALSE).
NOTE: All other information in the second VLOOKUP formula will be exactly the same as the first one. We are still looking for the value that will be placed in C2. We are still looking in the table named Customers. And we still want false for an exact match. The ONLY thing that is different is we are now going to pull the information from Column 3 instead of 2.
Move to cell C2 and type in one of the Account numbers you have in your Accounts sheet and you will see Excel fills in the Customer Name and Address for that account number However, if you type a number that does not exist on your Accounts sheet, Excel will leave the #N/A because the information for that number is "not available
To see a VLOOKUP formula in action, go to this page at my website and view this interactive formula:http://www.personal-computer-tutor.com/vlookup.htm
Now that we've gotten more comfortable with VLOOKUP, let's look at nesting formulas, one inside another. We can use the VLOOKUP formula we just made as a starter.
Let's say we don't want to see that nasty old #N/A every time one of our VLOOKUP formulas refers to an empty cell or value that is not in our lookup table. What we need to do is tell Excel to show us the result of the VLOOKUP only IF it does not return a #NA result and, otherwise, just leave the cell empty. So we need to use three different functions in one formula: VLOOKUP, IF, and ISNA.
Remember how we made an IF statement. =IF(<criteria>, <value if true>,<value if false>). If you don’t remember, read these two TechTrax articles I wrote:
Intro to IF Statements:http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=225
Nesting IF Statements:http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242
So, our criteria is IF the VLOOKUP returns #N/A, give me an empty cell, but if it doesn't, give me the result of the VLOOKUP.
Let's try it. First we need to think about each of the three formulas and what they must include. We already have our VLOOKUP formula and we already know what an IF function must include, so the only one we need to learn is the ISNA function. All the ISNA function does is return a value of true or false. True if the cell has a #N/A error and false if it doesn't. So, since an IF statement wants to know if something is true or false, these two functions work very well together.
Click into cell D2 in your Invoice sheet where your first VLOOKUP formula is.
The thing you need to understand is that when you nest formulas within formulas, Excel performs the operation dictated by the deepest nested formula first, then works its way outward. So, enter the following formula into cell D2, then enter an account number in cell C2 that does not exist on your Accounts sheet and you will see that the #N/A error does not show.
=IF(ISNA(VLOOKUP(C2,Customers,2,FALSE)),"",(VLOOKUP(C2,Customers,2,FALSE)))
NOTE: Be sure to include all the proper commas and parentheses. Remember that all opening parentheses must have closing parentheses or Excel will give you an error message.
Remember that Excel performs the deepest nested function first, so looking at the formula above, you will see:

· The first thing Excel does is perform the first VLOOKUP (since that's the one that is nested deepest)

· Then it performs the ISNA function on the result of the VLOOKUP to see if the VLOOKUP gave a #N/A error or not. The ISNA function returns a value of true if it sees the #N/A error and a value of false if it doesn't

· Then Excel performs the IF function on the result of the ISNA function. If it sees a "true", it returns nothing (which is what the "" tells it). If it is "false", it returns the result of the VLOOKUP function, which is what the last VLOOKUP function is telling it to do if the value is false.

Getting the hang of it? Excel has other lookup and reference formulas that you can explore using the Insert Function box. For example, HLOOKUP is similar to VLOOKUP, but it looks for Horizontally laid out data, instead of Vertical. More information on different lookup and reference formulas is available in this article from my ezine, ABC ~ All ‘Bout Computers:

Learning VLOOKUP in Excel

As September approaches, I can count on a series of spreadsheet questions. One of the popular Excel tutorial requests is how do you look up a value on one Excel worksheet and use it on another Excel worksheet. For example, you need to translate a product number into a product name. One of my favorite Excel functions is the VLOOKUP function and it can help with this task. (Includes Excel VLOOKUP Example file)

A recent case involved some voter registration data I needed to analyze. On one Excel spreadsheet, the voter’s party was listed as an alphanumeric value called "Pcode" and not the political party. This coding wasn't intuitive. For example, “D” was for “American Independent Party”, but some thought it meant “Democratic Party”.

One way to solve this problem is to create a worksheet with the Pcode and translation and have Excel use the VLOOKUP function for the party name. You might think of VLOOKUP as an Excel translator. I could then add a column called “Political Party” to my original worksheet to show the information from a lookup table.

Creating a Lookup Table

A lookup table includes the values you wish to "lookup" such as our Pcode and the translation such as political party. You can place this table on the same worksheet, but for this Excel tutorial I'll add a worksheet called "Political Party".
How to Create a Lookup Table,
1. Right-click your spreadsheet’s tab and select Insert…
2. On the Insert dialog, double-click Worksheet. This will be on the General tab.
3. Rename this new worksheet tab with a descriptive name such as “Party Codes”
4. In Column A, enter the unique values that exist on your main worksheet. In my example, these were the codes that showed in the Pcode column in the thumbnail. These values should be in ascending order.
5. In Column B, enter the translated value. You can have more values in column A than appear on your main spreadsheet. For example, I have an entry for “Citizen Party” even though I didn’t show a registered voter with that affiliation.

Using the VLOOKUP Function

Excel’s VLOOKUP function uses 4 pieces of information. The function panel may seem intimidating with the terms, but it’s simpler than it looks.
To lookup a value using VLOOKUP,
1. Add your new column on your original worksheet that will display the info pulled from the Lookup table. In my example, I added a column called Political Party in Column D. This is where I will insert the Excel function.

2. Place your cursor in the first blank cell in that column. In my example, this is cell D2.
3. From the Insert menu, select Function…. The Insert Function dialog will appear.

4. In the Search for a function: text box, type “vlookup” and click Go.
5. Highlight VLOOKUP and click OK.

Defining the VLOOKUP Values

After you click OK, Excel’s Function Arguments dialog appears and allows you to define the four values. You’ll see that your starting cell and the formula bar show the beginning part of the function =VLOOKUP(). The Function Arguments dialog adds the needed data elements that will display between ().
For illustration purposes, I have overlaid the Party Codes worksheet on top to show the relationships.

1. Lookup_value – Think of this field as your starting point. In my example, I’ll click cell C2 so the value is filled in the dialog. I'm requesting Excel take the value of C2, which displays as the Pcode of “A”, and find the matching political party on my lookup table on the Party Codes worksheet.
2. Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our “Party Codes”. When you click another tab and define the range, Excel prepends that tab name to the range such as ‘Party Codes’.

Rules & Caveats

There are several rules to remember about this table array.

Rule 1 - The left column must contain the values being referenced. In other words, I couldn’t have our first column be Political Party.

Rule 2 - You can’t have duplicate values in the leftmost column of the lookup range. I couldn’t have two entries with the value “A” with one being “Democratic” party and another “A” for the “Humanist” party. Excel would complain.

Rule 3 - When referencing a lookup table, you don’t want your cell references to change when you drag and fill to populate the other cells with the VLOOKUP function. As example, if I want to use the same function in cells D3 through D7, I don’t want my lookup cell references to shift each time I move down to the next cell. I need the cell references to be the same. After you define your range, you need to press F4 which will cycle through absolute and relative references. You want to select the option that includes a $ before your Column and Row. ( 'Party Codes'!$A$2:$B$45. ) You can get around this if you know how to use Excel name ranges.

Col_index_num – This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Party Codes worksheet which has the name of the political party.
Range-lookup – this field defines how close a match should exist between your Lookup_value (C2) and the value in the leftmost column on our lookup table. In our case, we want an exact match so we’ll use “FALSE”.
After clicking various cells, my dialog looks like this:

You can see in the circled formula bar above, I now have more information based on my entries in the Function Arguments dialog box.
The other item of interest is that when you build these functions, Excel displays the result in the Formula result = text line. This is great feedback which can show if your function is on target. In our example, we can see Excel looked up the Pcode of “A” and returned the Political Party “Democratic”.

Copying the VLOOKUP Function to Other Cells

It doesn’t make sense to use VLOOKUP for one cell in your Excel spreadsheet. Instead, I want to copy the function to other cells in the same column.
To copy VLOOKUP to other column cells,
1. Click the cell containing the VLOOKUP arguments. In our example, this would be D2.
2. Grab the cell handle that displays in the lower right corner.
3. Left-click and drag down the cell handle to cover your column range.
Note: If I hadn’t changed to absolute reference as mentioned in Rule 3, I would’ve seen my table array entry shift by one cell as we dragged down through the other cells.
VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, I used a 1:1 code translation, but you could also use it for group assignments. For example, you could assign state codes to a region such as CT, VT, and MA to a region called “New England”. And for the adventurous, you can use VLOOKUP in your Excel formulas.