The next pairs of arguments specify that Excel should look through D2:D57 for ages that are greater than zero. Note the single dollar sign before the 1 in I$1. This lets you copy the formula. This argument will always point to the criteria in row 1, but the reference can change to column J, K, L, and M.

The next pair of arguments say to look through the ages in D2:D57 for ages less than 30. Again, the criteria is stored in I$2.

The final pair of arguments say to look through the departments in E2:E57 looking for records that match Accounting as stored in $H3.

Gotcha: The #DIV/0! in M6 is because the sales department has no employees above 59 years of age. (Probably because the sales reps have retired to a private island after earning those huge commissions for so long.) When you AVERAGE a range that contains no numeric cells, you end up dividing by zero.

AVOID ERRORS USING IFERROR

Problem: I’ve written a brilliant formula. Sometimes, due to the incoming data, the formula generates an error. How can I suppress the errors?

Strategy: Starting in Excel 2007, Excel offers the amazing IFERROR function. Don’t confuse this with the =ISERROR(), =ISERR(), =ISNA() functions. This new IFERROR function is very cool.

Before Excel 2007, to head off errors, you would have to test for any of the conditions that would cause an error. This might mean taking the very long formula from Figure 393 and making it insanely long:

=IF(COUNTIFS($D$2:$D$57,I$1,$D$2:$D$57,I$2,$E$2:$E$57,$H3)=0,”--”,AVERAGEIFS($F$2:$F$57,$ D$2:$D$57,I$1,$D$2:$D$57,I$2,$E$2:$E$57,$H3))

There are 20 cells in Figure 393, and 19 of them are working just fine. Yet, this formula will force Excel to do a complete SUMIFS before it can figure out if it should put -- or go on to calculate the AVERAGEIFS. This a huge amount of complexity just to zap one #DIV/0! error.

Figure 394 Before Excel 2007, error handling was slow and complex.

Handling errors with IFERROR is dramatically easier. Say that you have any formula. Edit the formula. Type IFERROR( after the existing equals sign. Go to the end of the formula, type a comma, then what you want to have happen in case there is an error, then the closing parenthesis.

Say that you had a formula of =Formula.

●● To replace errors with a zero, use =IFERROR(Formula,0) ●● To replace errors with --, use =IFERROR(Formula,”--”)

●● You can specify another formula: =IFERROR(Formula,OtherFormula).

Think about most data sets where some errors occur. You probably have more than 95% of the cells that calculate without an error and less than 5% that generate errors. The IFERROR function is smart enough to try the first calculation and only move on to the second argument when it gets an error. This will be a drastic time savings when you don’t have to use =IF(ISNA(VLOOKUP()),”--”,VLOOKUP()) anymore.

Figure 395 IFERROR simplifies error checking.

Additional Information: Excel 2013 adds the =IFNA() function. Say that you want to catch any VLOOK- UP functions that return #N/A, but you want to allow any underlying DIV/0 errors to show through. The IFNA in Excel 2013 will only convert #N/A to the alternate value.

USE VLOOKUP TO JOIN TWO TABLES

Problem: My I.T. department gave me a data set with Item Number, Date, and

Quantity sold. They didn’t put an item de- 2 scription in there. If I call back and ask

them to re-do the file, it will take 3 weeks. Can I quickly fill in the item descriptions?

Figure 396 You need item description here.

I have a second table that maps item number to item description.

Strategy: Use VLOOKUP. This is the single most important function in Excel. VLOOK-

UP will save you time and time again.

Figure 397 You have a table with the item descriptions..

In this situation, VLOOKUP requires four arguments: ●● The value to look up. In this case, it is cell A2.

●● A table with the lookup value in column 1 of the table. You always want to enter the table with dollar signs throughout the reference. That way, you can copy the VLOOKUP formula to other rows. In this case, the table is in $L$3:$M$30.

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

●● The fourth argument in VLOOKUP is always FALSE. If you don’t put a FALSE as the fourth argu- ment, 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,$L$3:$M$30,2,FALSE).

Additional Details: You can use a wildcard in VLOOKUP. To find the first entry that starts with A, use =VLOOKUP(“A*”,Table,2,False).

Figure 398 If you can enter the VLOOKUP with your eyes closed, I will give you a spot on my team when Excel becomes a sport in the Olympics.

Additional Details: I used to be a manager of financial analysis. On the job posting form when I was hiring financial analysts, I would list a single requirement: “Can do VLOOKUPs in your sleep”. It really is the single most important Excel skill.

Additional Details: To me, the world breaks down into two kinds of people, those who can do VLOOK-

UPs, and everyone else.

EVERY VLOOKUP ENDS IN FALSE

Problem: My VLOOKUPs aren’t working. It is returning other values from the table.

Strategy: You have to end your VLOOKUPs with FALSE as the fourth argument. If you don’t put FALSE at the end of your VLOOKUP, then you are using a completely different function. There are people who leave the FALSE off the VLOOKUP. If you don’t specify FALSE, then you are letting Excel assume that you want TRUE as the range_lookup 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 as shown all the way back in Figure 377.

●● 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 loop hole in the range_lookup version of VLOOKUP to re- turn the last non-blank value in a row or column.

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