Avoid Excel Lookup Errors Forever

We’ve all had that nasty feeling — our vlookup formula returns an error, leaving a screaming #N/A where you had expected to see numbers.  This is usually because of some loophole in your dataset — for some reason, one of the entries for which you are looking up a value does not have a match.

This post assumes you have some knowledge on how to write a vlookup — for the basics, check out this tutorial from Microsoft.

In our example, we have the day’s sales for 50 or so stores, and we want to find a few of their sales.  Vlookup is a good formula for this.   We use a simple vlookup to find the store’s sales.

vlookup

But there is a curveball.  Store 57 isn’t giving us a value!  This makes a pretty ugly result — it throws out our total.  We find out that Store 57 is closed.  They had 0 sales, not “N/A.”  But because there wasn’t a store 57 in your source data, Εxcel returns an “N/A.”

There is a way to fix these formula meltdowns — it’s called IFERROR.  Start your new formula with =IFERROR(.  You will see the below pop up:

=IFERROR(value,value_if_error)

Translated, this means Excel will give you the result you ask for (the first part of the formula), unless there is an error, in which case it will give you whatever you want in the second part of the formula.  We want to get the result of a vlookup, unless there is an error, in which case we want a 0.

Your new formula uses the same Vlookup, but there is cushion on both sides now — to avoid the dread N/A.

iferror(vlookup

I have made a habit of coding all of my vlookups this way.  If an entry is not getting a match, then I default its value to 0.  This avoids the ugliness of an error.

Subscribe to my mailing list.

Leave a Reply

%d bloggers like this: