Reputation: 6476
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
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