IFERROR Returning 0: Two Quick Solutions for Excel Users

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Lookups and return errors are like salt and pepper, PB & J, or whatever analogy you want to pick to describe a relationship destined to be together. The larger the dataset, the more likely your VLOOKUP, XLOOKUP, or whatever is going to hit some roadblocks. That’s why the IFERROR function is such a useful addition to your formula.

But what happens when you find your IFERROR returning zeros, making you question your sanity and diving deep into the Microsoft Support forum? The answer to your problem is an easy one.

When a 0 is returned with an IFERROR function, it’s because a match was found, and the value of the cell is blank. And just because it’s blank does not make it an error. So, IFERROR doesn’t know to return a blank value in its place.

Now you might be wondering why it doesn’t return a blank cell since the origin location is also blank. What’s with the zero? That’s a good question and one I’ll explain. Stick around, and you’ll see just how easy it is to fix this inconvenience now and in the future.

Why is the IFERROR Returning a Zero in Excel?

IFERROR returning 0 in part list

Let’s recap.

If your lookup is returning a zero, even if you used an IFERROR function, it’s because your lookup value exists in the lookup range, but the value of the cell is blank.

The reason Excel replaces that blank with a zero is because Excel’s default setting is to show all values in a cell. If a match was found, it technically has a value of nothing…but still a value. And Excel represents that nothing as 0.

So the truth is, the lookup was successful, and no error was generated.

IFERROR is used to replace a return error from a completed formula using the syntax =IFERROR(value, display “this” if an error is generated). Generally, the type of Excel errors you’d expect to encounter are #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, or #NULL!.

As I said, a blank cell does not equate to an invalid return, so you get a zero.

How Do I Show a Blank Cell Instead of a 0?

Ok, ok. Let’s not keep going around in circles. I think you’ve got the point of the why and how. So what are your options? What can you do to show a blank cell instead of a zero?

The easiest way to get rid of the 0 in your cell is to change the default setting in your Excel Worksheet. Simply go to the File tab and select the Options button toward the bottom. When the Options window appears, choose Advanced from the left panel menu.

Next, scroll down to the ‘Display options for this worksheet’ and uncheck the box that controls the display of all zero values. Hit ok, and now you’ll find that all those zeros have converted to empty cells.

Advanced options to hide zeros

The second way to replace 0’s with blank cells is to add a bit more to your formula. There are actually a few different ways you can structure it to get the same desired effect. And one way is to use an IF statement.

In the formula below, I added the IF statement to check if the VLOOKUP finds a match on a blank cell. If it does, it displays the return as empty instead of returning the lookup range cell. If the match contains a value other than a blank cell, it returns the value. And thanks to the IFERROR function, if it were to return a #N/A or #VALUE! error, it’s replaced with a blank cell.

NEW FORMULA TO REPLACE 0 IN THE IFERROR FUNCTION

And there you have it! Again, there are other nested functions you can use or even formatting tricks to achieve the same outcome. So feel free to play around and figure out what works best for you.

1 thought on “IFERROR Returning 0: Two Quick Solutions for Excel Users”

Leave a Comment