CODEWITHSUNDEEP
excellookup
Cool Blue
Cool Blue

Reputation: 6476

Worksheet functions returning 0 when the actual value is ""

For some time I've been aware that excel make a last minute decision to return 0 in lookup functions when the actual target value is "empty". I know that the actual function returns the correct value because I got into the habit of using If(lookupfunction="","",lookupfunction), which is a pain but works.

I have a table with a vlookup in a calculated column which looks up another table based on a third table with a parallel structure (using tablename[@[name]] style referencing). Some of the entries in the source table are blank and the lookup was returning blank (to my surprise and delight). I then deleted one of the non-blank entries in the source table and the vlookup returned 0 instead of "", just for the one I deleted. If I delete another entry, it also reverts to zero instead of "". I checked all of the formatting and it is all set to general so the zeros are not being hidden.

I guess that is by way of a bug report, but my question is: is there a neat way to pass "" through worksheet functions without them being converted to 0 all the time

Upvotes: 0

Views: 10743

Answers (1)

Charles Williams
Charles Williams

Reputation: 23550

Unfortunately Excel formulas cannot return an Empty cell - empty gets coerced to zero.
A cell containing ' or "" or space is not an empty cell but contains a zero-length string: functions like Lookups will happily return a zero-length string - it looks like an empty cell but is not.

Upvotes: 1

Related Questions