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

edit retag close merge delete

1

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.

( 2021-03-05 06:42:51 +0200 )edit

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, @Earnest Al 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.

( 2021-03-05 07:06:43 +0200 )edit

Sort by » oldest newest most voted

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.

Help Page N()

more