Ask Your Question

iferror formula returns blank not 0

asked 2021-03-05 05:27:09 +0200

C gravatar image

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 flag offensive close merge delete



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.

Earnest Al gravatar imageEarnest Al ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 2021-03-05 07:06:43 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-03-05 18:35:06 +0200

dscheikey gravatar image

updated 2021-03-05 18:55:55 +0200

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.


I hope this helps.

Help Page N()

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-03-05 05:27:09 +0200

Seen: 36 times

Last updated: Mar 05