Nested IF() statement with FIND() text is returning #VALUE error

userukC4 0
Viewed 12

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())))

if-statement excel
1 Answers

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())))
Scott Craner0
Related