Read an Excerpt

VLOOKUP: Awesome Quick

From Your First Excel VLOOKUP to Becoming a VLOOKUP Guru

Holy Macro! Books

THE BASICS

USE VLOOKUP TO JOIN TWO TABLES

Your I.T. department gave you a data set with Item Number, Date, and Quantity sold. They didn't put an item description in there. If you call back and ask them to re-do the file, it will take three weeks.

You look around on your computer and you find a second table that maps item number to item description. It is fine that the lookup table contains extra columns, such as Price in the figure below. It is important that the lookup value is to the left of the value that you are trying to return. In the figure below, SKU in column F is located left of Description in column H, so the table will work.

For now, copy this table out to the right of your data set. You should leave at least one blank column between the original data set and the lookup table.

VLOOKUP will solve this problem easily. This is the classic use for VLOOKUP. This is the single most important function in Excel. VLOOKUP will save you time and time again.

To do a VLOOKUP, you specify a lookup value such as cell A2. You also specify a lookup table such as F2:H30.

The lookup table must have the value you are trying to lookup in the left-most column of the table. Excel will take the value in A2 (in this case W25-6) and cruise down through the left-most column of the lookup table. When Excel finds an exact match, it will return one of the columns from the matching row.

VLOOKUP requires four arguments:

• The value to look up. In this case, it is cell A2.

• A lookup table. This table must have the values that you are looking up in the left-most column. In this case, the table is in F2:H29. However, you always want to enter the lookup table range with dollar signs throughout the reference. That way, you can copy the VLOOKUP formula to other rows. In this case, the table is in $F$2:$H$29.

• A column number in the table to return. Unlike Lotus 1-2-3, Excel will allow you to return the first column in the table, so the SKU column above would be column #1. The Price is column #2. The Description column is column 3.

• The fourth argument in VLOOKUP is always FALSE. If you don't put a FALSE as the fourth argument, Excel will give you results that almost match. This is NEVER what you want when you are solving these types of problems.

The formula for D2 is =VLOOKUP(A2,$F$2:$H$29,3,FALSE).

Copy the formula in D2 down to all of the other rows in your data set. For a fast way to copy the formula, select cell D2 and double click the square dot in the lower right corner of the cell. This square dot is called the Fill Handle. Double-clicking it will copy the formula down until Excel detects the end of the data set. Since you were careful to specify the lookup table with dollar signs, the formula will copy correctly. Take a look at the copied formula in cell D9

In cell D9, the copied formula is looking for the BR15-3 from cell A9. It looks through the table in F2:H29. The match is found in the first column of the lookup table, in row 8. Thus, VLOOKUP will be returning one of the values from F8:H8. Since the formula asks for column 3, the VLOOKUP returns the description from H8 to cell D9.

COPY THE VLOOKUP TO GET PRICE, TOO

Say that you like the VLOOKUP so much that you want to add two new columns to the original table to show unit price and total price. Insert two new columns to the left of column E.

First, notice that the formula in D2 is automatically rewritten to reflect the fact that the lookup table has moved over to columns H:J:

[ILLUSTRATION OMITTED]

However, if you try to copy cell D2 to E2, you will get the dreaded #N/A error.

Look at the formula in Figure 6. It is now trying to lookup the Date in B2 instead of the Item Number from A2. You could have prevented this problem by writing the original formula as $A2, but it is easy enough to edit the formula here in C2. Change the B2 to A2. Also, change the third argument from 3 to 2 to indicate that you want the price here instead of the Description.

The Total Price in F2 is =E2*B2. Copy the formulas from E2:F2 down to all rows and you've calculated total price as well.

99% OF VLOOKUPS END IN FALSE

Unless you are a scientist or a commission accountant, all of your VLOOKUPs will end with FALSE. Get in the habit of ending every VLOOKUP with FALSE).

There is a tiny segment of the population who will not have FALSE at the end of their VLOOKUP. When you use VLOOKUP without the FALSE at the end, you are doing something completely different. Microsoft should not even call it a VLOOKUP. It should be called VLookForSomethingSortOfClose, but I guess this is too long for scientists to type.

I will tell you later in Chapter 6 about the use for VLOOKUP without FALSE, but for now, repeat the mantra: Every VLOOKUP Ends in FALSE.

If you don't specify FALSE, then you are letting Excel assume that you want TRUE as the fourth argument. You are asking for trouble. You will hate VLOOKUP when you e-mail an incorrect document to the entire department. Don't ever write a VLOOKUP that does not end in FALSE.

The only exceptions:

• Some people put a zero instead of FALSE. That's fine. It saves you 4 characters of typing, but it still runs the FALSE version of VLOOKUP.

• Commission accountants have permission to use the approximate version of VLOOKUP, but only 1% of the time. They must specifically be trying to eliminate a bunch of nested IF statements.

• Scientists — I get it. They do range lookups all the time. If you are a scientist reading this book, send it back to me and I will send you Gerry Verschuuren's Excel for Scientists book.

• Very clever Excel tricksters will utilize a loophole in the range_lookup version of VLOOKUP to return the last non-blank value in a row or column.

Everyone else should be using the FALSE version of VLOOKUP every time.

THE LOOKUP TABLE DOES NOT HAVE TO BE SORTED

Problem: You heard once that the lookup table has to be sorted.

I don't care what your professor said, if you are using the FALSE version of VLOOKUP, the lookup table does not have to be sorted.

Since 99.9826% of VLOOKUPs have FALSE at the end, the table does not have to be sorted in 5759 out of every 5760 cases. The one exception usually only applied to Scientists, Commission Accountants and certain Excel Tricksters. Perhaps we should abbreviate this group as the SCAXLT.

Sure, when you don't have FALSE (or 0) at the end of your lookup table, then the table has to be sorted.

However, most of the time, the lookup table does not have to be sorted.

TROUBLESHOOTING VLOOKUP

I can already tell you that you are looking at this chapter because your VLOOKUP is returning the #N/A error. However, this is a symptom that can be caused by one of several problems. To find the cure, see which one of these applies to you:

• If most VLOOKUPs are working, but you are getting a few scattered #N/A, mostly on newer products, then tap here.

• If there are no #N/A at the top of your data, but the #N/A start happening with frequency as you move down the rows, you most likely forgot the dollar signs in the lookup table reference. Tap here.

• If every VLOOKUP is returning #N/A, then tap here.

• If the VLOOKUPs are working for text cells, but failing for the numbers, tap here.

BEWARE OF #N/A FROM VLOOKUP

Problem: A few of my VLOOKUPs are giving me the #N/A error.

Strategy: This is common when you are doing VLOOKUP. It tells you that the lookup value is not found in the first column of the table. When you encounter an #N/A error, add that item to the table (see the next topic).

Additional Details: To isolate the #N/A errors, sort your data descending (using the ZA icon). All of the #N/A errors will sort to the top.

Gotcha: If you leave the #N/A errors in the data set and try to add up that column, the SUM will be #N/A. One single #N/A causes all downline formulas to calculate as #N/A. You need to fix the #N/A's or they will cause problems.

ADD NEW ITEMS TO THE MIDDLE OF YOUR LOOKUP TABLE

Problem: I have to add BG33-9 to my lookup table. When I enter it in the blank row below my lookup table, the #N/A error does not go away.

Strategy: You would have to rewrite the VLOOKUP to include the new extra row. For example, if the lookup table is currently $F$2:$H$29, you would change the formula to now include row 30 in the lookup table. But, it is a hassle to rewrite your formulas, so, instead, you could use any of these clever strategies:

• Insert new cells anywhere in the middle of your lookup table. For example choose F7:H7 and do Alt+I+E followed by Enter. This will Insert Cells and shift the remaining items down.

• Specify F:H as the lookup table. This uses the whole column as the lookup table. Now, you can add items to the bottom without rewriting the formula. Excel is smart enough to only use the non-blank cells when calculating.

• Select one cell in the lookup table and press Ctrl+T before you add the new value. This declares the range as a Table. When you type new values in the empty row below the table, any formulas that point to the table will automatically expand to include the new row. In one of those scary bits of Excel magic, they actually rewrite your formulas to point to the extra row in the VLOOKUP formula. This happens even if you are not using Table Formula Nomenclature.

AVOIDING #N/A USING IFERROR OR IF(ISNA

Problem: Wait, I don't really care about the #N/A items. If they aren't found, I would rather just have a blank appear or a zero or something like that. I know the data well and the #N/A items are custom one-off items that I don't need to look at today. I really don't want to have to add the specials to my lookup table.

Strategy: There are two different ways to solve this problem. If you are using Excel 2007 or newer, and everyone who opens the workbook is using Excel 2007 or newer, you can use the new =IFERROR() function. If the workbook still needs to work back in Excel 2003, you can avoid the #N/A errors using a combination of IF and ISNA functions.

If you have Excel 2007 or newer, go to the first VLOOKUP formula. Press F2 to put the cell in Edit mode. Press the Home key to move to the beginning of the formula. Press the right arrow key to move to after the equals sign. Type the following characters:

IFERROR(

Then, press the End key to move to the end of the formula.

To replace the #N/A with 0, type a comma, a zero and a closing parenthesis.

To replace the #N/A with a blank, type a comma, two quotation marks " and a closing parenthesis.

To replace the #N/A with the words No Dice, type a comma, "No Dice").

Press Ctrl+Enter to accept the formula and stay in the current cell. Double-click the fill handle to copy the formula down.

To recap: if your formula before is:

=VLOOKUP($A2,$H$2:$J$30,2,False)

then your formula after might be:

=IFERROR(VLOOKUP($A2,$H$2:$J$30,2,False),0)

The IFERROR function was introduced in Excel 2007 and is a huge improvement over the previous method. IFERROR is very fast. With IFERROR, Excel first calculates the formula in the first argument. 99% of the time, that formula results in a valid answer and then Excel just stops calculating that cell. It is only in the rare cases that you have a non-match that Excel has to move on and replace the VLOOKUP with the zero or blank or "No Dice".

Before Excel 2007, you would have to change the formula like this:

=VLOOKUP($A2,$H$2:$J$30,2,False)

to:

=IF(ISNA(VLOOKUP($A2,$H$2:$J$30,2,False)),0, VLOOKUP($A2,$H$2:$J$30,2,False))

This is a horrible formula to enter and it takes twice as long for Excel to calculate. For every cell in your data set, Excel will do the VLOOKUP, see if it is N/A, If it is N/A, then you get a zero. If it is not N/A, then Excel does the VLOOKUP all over again! With thousands of cells of VLOOKUP it can add a lot of calculation time to your workbook.

Nevertheless, if you are back in Excel 2003, I do have somewhat faster way to enter this monstrosity of a formula.

1. Select the cell with the first VLOOKUP.

2. Press F2 to put the cell in Edit mode.

3. Press the Home key and the right key to move after the equals sign.

4. Type IF(ISNA(

5. Press Ctrl+Shift+End to select the characters in the rest of the formula

6. Press Ctrl+C to copy that formula fragment to the clipboard.

7. Press the End key to move to the end of the formula.

8. Type ),0,

9. Press Ctrl+V to paste the VLOOKUP formula fragment into the third argument of the IF function.

10. Type a closing parenthesis

11. Press Ctrl+Enter to accept the formula and stay in the same cell.

12. Double-click the fill handle to copy the newly edited formula down.

That seems like a daunting set of steps, doesn't it? It is a lot faster than trying to type the whole formula. Plus, when you get good at it, you can do the whole thing in a few seconds, like this video:

[ILLUSTRATION OMITTED]

(Yes, I make that look easy, but I've probably done it thousands of times.)

REMOVE LEADING AND TRAILING SPACES

Problem: None of my VLOOKUP formulas are working. I can clearly see that there is a match in the lookup table, but Excel can not see it.

NOTE: When you encounter this problem, you will likely have almost ALL #N/A errors, as opposed to just a few #N/A errors as in the previous topic.

Strategy: A common problem is that either the item in column A or Column F has trailing spaces. This can happen if you downloaded the data from another system. Old mainframe computers running a programming language called COBOL are notorious for putting trailing spaces at the end of a value.

To diagnose this problem, select cell A2 and press the F2 key to put the cell in Edit mode. A flashing insertion cursor will appear at the end of the cell. Check to see if the insertion cursor appears immediately after the last character or a few spaces away.

Edit cell F3 to see if there are trailing spaces. You will likely find that either column has trailing spaces. Below, you can see that there are a couple of trailing spaces after the Item in column A. These trailing spaces cause the VLOOKUP to not classify the cells as a match. Although you can tell that "BG33-8" is the same as "BG33-8", Excel cannot.

You can use the TRIM function to remove leading and trailing spaces from a value. If there are spaces between words, it will change consecutive spaces to a single space. For example, =TRIM(" Bill Jelen ") would change the cell contents to "Bill Jelen".

Additional Details: If the trailing spaces appear in your lookup value, use TRIM around that one value. Change:

=VLOOKUP(A2,$F$3:$G$30,2,FALSE) to =VLOOKUP(TRIM(A2),$F$3:$G$30,2,FALSE).

If the trailing spaces appear in the lookup table, then you can actually TRIM the entire table with one bizarre modification. Change the formula above to =VLOOKUP(A2,TRIM($F$3:$G$30),2,FALSE)

But, don't press Enter after making the edit. Instead, hold down Ctrl and Shift and then press Enter.

Gotcha: That formula where you TRIM the entire lookup table is going to be insanely slow. It is fine for impressing your friends who use Excel, but in real life, it would be better to add a temporary column to TRIM each individual cell in column F. Then, copy that column and paste as values over column F. Here are the steps to do this:

1. Go to a blank cell to the right of the lookup table. Perhaps H3.

2. Type a formula of =TRIM(F3). Press Ctrl+Enter

3. Double-click the fill handle to copy the formula down. Also, H3:H30 will remain highlighted.

4. Ctrl+C to copy the range of formulas

5. Select cell F3. Select Home, Paste dropdown, Values to paste the trimmed key field as values.

6. At this point, you can delete the temporary formulas in column H.

Alternate Strategy: If you have all #N/A errors and leading or trailing spaces are not the issue, then you might have a situation where you have numbers stored as text. A numeric 3446 will not match a text 3446. See the next topic to solve this problem.

NUMBERS AND TEXT NUMBERS WON'T MATCH

Problem: Some of my VLOOKUPs are working, but many are not working.

---

Excerpted from VLOOKUP: Awesome Quick by Bill Jelen, Tyler Nash. Copyright © 2012 Bill Jelen. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---