It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.

In the image below cell F2 has a VLOOKUP to extract the value.

This works while a valid entry is in cell D2.

If D2 has no entry the VLOOKUP returns an error – see below.

To handle a blank input cell we add an IF function to display a blank cell if D2 is blank, but that breaks the multiplication calculation in cell G2, see below.

We use two quotation marks together “” to identify and display a blank cell.

The fix for cell G2 is easy and short. The formula is

=F2*E2

And we change it to

=N(F2)*E2

The N function changes all text to 0 so now the multiplication works. The IF function returns text when it uses “”.

A valid code works as usual.