The basic need is to evaluate a cell [@[Products Purchased]] and return one of 3 values ("HF", "H", or "F") based on the text. I am using FIND() since I don’t think IF() allows wildcards.
The issue is that only the first value "HF" is ever returned if TRUE. If FALSE then I get the #VALUE error. The nested IF() statements work fine by themselves in other columns except for the fact that a FALSE will return the same #VALUE error.
The formula:
=IF(AND(FIND("ish",[@[Products Purchased]])>1,FIND("unt",[@[Products Purchased]])>0),"HF", IF(FIND("ish",[@[Products Purchased]])>0,"F",IF(FIND("unt",[@[Products Purchased]])>0,"H",NA())))
>Solution :
FIND returns the relative starting number of the matched string or an error if the string is not found.
Instead of >0 we need to use ISNUMBER() to test if the value was found in the string:
=IF(AND(ISNUMBER(FIND("ish",[@[Products Purchased]])),ISNUMBER(FIND("unt",[@[Products Purchased]]))),"HF", IF(ISNUMBER(FIND("ish",[@[Products Purchased]])),"F",IF(ISNUMBER(FIND("unt",[@[Products Purchased]])),"H",NA())))