I just switched from excel to calc recently
The formula used in both excel and calc “=IFERROR(HLOOKUP(P$4,$Sheet1.$B$1:$S$108,7),0)”
In excel it will return 0 if value on sheet1 is blank
However in Calc when I use the same formula if the value on sheet1 is blank it will return as just blank
How do I get Calc to return as 0 if there is no value on sheet1? Thank you
You should look at the Help on HLOOKUP and especially the link to Handling of Empty Cells. It seems that Excel returns zero for empty cell anyway.
Yes you are right. A formula like =VLOOKUP(1;A1:B2;2)
, where A1
contains 1, and B1
is empty, gives “empty” cell in Calc, and 0 in Excel.
I don’t know if that’s permitted (I can’t find it in OpenFormula), but you are welcome to file it as a bug, as an inconsistency with both Excel, and with references like =B1
, which return 0 when B1 is empty.
Edit: Oh, @EarnestAl is absolutely right!
Anyway, note that this is "*LOOKUP
returning empty", not "IFERROR
returning empty", since what you see is only when *LOOKUP
does not return an error.
Hi “C”,
You can use the function =N() to achieve the desired effect. However, texts are also output as zero. In the case of an empty cell or text = 0 (because of the iferror function), the value for digits.
=N(IFERROR(HLOOKUP(P$4,$Sheet1.$B$1:$S$108,7),0))
I hope this helps.