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.