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
And we change it to
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.