iferror formula returns blank not 0
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), whereA1contains 1, andB1is 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, @Earnest Al is absolutely right!
Anyway, note that this is "
*LOOKUPreturning empty", not "IFERRORreturning empty", since what you see is only when*LOOKUPdoes not return an error.