Download the exercise file here.
You have a pretty slick vlookup now. You are adding to the efficiency and the accuracy of your work. Bosses will notice. It is time to put the cap on your vlookup modification.
A good analyst expects the unexpected in data. When writing a vlookup to get data, assume something will go wrong. Let’s look how to prevent one of the most common vlookup headaches — a missing data point.
Your boss has asked you to look up numbers on a new store, Number 26. Easy enough. Just put 26 into Cell F6 and drag your vlookups down. Wait — that didn’t work? Didn’t we adjust for all the inadequacies of a simple vlookup?
Check your work again — carefully. The good data analyst questions every input. Your cell references are rock-solid, so there has to be an issue in the source data. Check Column B. Store 26 doesn’t exist! Apparently, the store was closed.
Now you have a nasty-looking error in your spreadsheet. You might think to just hard-code or key a zero over the error, or leave the cell blank. Do NOT do this. Inconsistent formulas across cells will cause confusion and error.
How can we avoid hard-coding yet still get an appropriate vlookup answer?
Welcome to the IFERROR formula.
What IFERROR does
If your formula doesn’t return an error, run it. If it does return an error, do something else.
In our case, we want to use a vlookup only if it returns no errors. If there is an error, we want a 0. This works because the store’s sales are 0 if it is closed.
You will see =IFERROR(value,value_if_error). Let’s break down this formula.
Value: This is the vlookup part. You know how to write this…go ahead and do so.
Value_if_error: This part is simple — 0. You want to return a 0 for any vlookups that return an error.
Apply your formula to your data. Notice that Store 26 now returns 0’s instead of #N/A’s.
Look at you, savvy business analyst! You have built an error-proof vlookup. You can drop and drag the formula across your spreadsheets and never return an error. The less time spent troubleshooting formulas in Excel, the better.
Leave a Reply