When building formulas you sometimes need to identify a blank cell. Let’s say you use a VLOOKUP function. If the cell you are looking up is blank it can cause the #N/A error.

There are a couple of techniques you can employ to identify a blank cell.

=IF(A1="","",VLOOKUP(A1,$D$1:$F$10,2,0))

Or

=IF(LEN(A1)=0,"",VLOOKUP(A1,$D$1:$F$10,2,0))

The first one uses the “” to identify the blank cell. In Excel two inverted commas represent a blank.

The second formula uses the LEN function (short for LENGTH) to see if there are any characters in the cell. Comparing to zero identifies if there are no characters in the cell and hence that means the cell is empty.

Note: both of these formulas avoid the #N/A due to a blank look up cell, BUT they DO NOT avoid the #N/A caused by an incorrect code. See final formulas for that.

Both of these formulas handle the situation where cell A1 contains a formula that returns “”.

But both of these formulas DO NOT handle the situation where there is an Excel error message in cell A1. They will both display that error message.

That’s where the ISBLANK function comes in. The ISBLANK function will identify any cell that doesn’t have an entry. If a cell is blank the ISBLANK function returns TRUE. If a cell contains a formula that returns “” it is NOT treated as blank by ISBLANK and will return FALSE. After all, the cell has a formula in it so it’s not blank. ISBLANK ignores Excel’s error messages.

So the above formulas could be re-written as:

=IF(ISBLANK(A1),"",VLOOKUP(A1,$D$1:$F$100,2,0))

Now, if there is an error in cell A1 the above formula will still return that error, but it is the VLOOKUP function that is returning the error, not the ISBLANK function.

So ultimately to avoid all errors in Excel 2007 and later versions you would use:

=IF(ISBLANK(A1),"",IFERROR(VLOOKUP(A1,$D$1:$F$10,2,0),"Missing"))

The IFERROR function was introduced in Excel 2007 to simplify error handling.

The Excel 2003 version of the above formula is

=IF(ISBLANK(A1),"",IF(ISERROR(VLOOKUP(A1,$D$1:$F$10,2,0)),"Missing",VLOOKUP(A1,$D$1:$F$10,2,0)))

You can see that the IFERROR version is a lot easier to use and understand.

If you want to identify the #N/A error specifically, you can use the ISNA function. The ISERR function traps all the non #N/A errors.