IF function issue in Calc

I’m trying to search a string for text and set another cell’s value based on whether or not it was found. For simplicity I have tried to make it work in only a 1 case as follows:


When A1 contains the word “hello” a 1 appears in B2, however if it doesn’t I get #VALUE! instead of the 2 I was expecting. I’ve obviously misunderstood something about the IF function, I just can’t figure out what or why. I am using Version: of Calc.

Thanks for your help

It also happens in Openoffice 4.1.3. It’s not the IF function but the FIND function that causes the problem. See Calc: FIND function for a full explanation and solution.

If this answer helped you, please vote it with :heavy_check_mark: (here on the left). That will help other people with the same question.

Thanks very much, the LibreOffice Wiki page does not specify return values, an incredibly frustrating omission when trying to debug a problem. The ISERROR and ISNUMBER modifiers work perfectly.

A hint: open the Function Wizard (Ctrl+F2) on a formula cell to inspect details. In this case on the Structure page it tells you that already the FIND() function returns #VALUE! error.