SOLVED

Getting an error when using nested IF function

Copper Contributor

I'm trying to sort through a column of remarks. If any of the remarks contain one word, I need to pull that word out. If it says a different word, I need to pull that one out. No remark should have both, but some will have neither. If there is neither, it can be left blank. 
I've tried to use
=IF((FIND("FWA",DATA!AM7)), "FWA",
IF(FIND("JBER",DATA!AM7),"JBER"))

which will work for the first value, but for the second I get a "#VALUE!" error. 

What am I doing wrong here? As far as I can tell the syntax is legit. 

2 Replies
best response confirmed by GlintF (Copper Contributor)
Solution
The problem is that FIND() will return #VALUE! if not found. So enclose that in ISNUMBER() or IFERROR( ... , 0) like:
=IF(ISNUMBER(FIND("FWA",DATA!AM7)), "FWA", IF( ISNUMBER(FIND("JBER",DATA!AM7)),"JBER",""))
and I added a "" if not found
Oh, duh. Thank you, I should have realized that. I appreciate it.
1 best response

Accepted Solutions
best response confirmed by GlintF (Copper Contributor)
Solution
The problem is that FIND() will return #VALUE! if not found. So enclose that in ISNUMBER() or IFERROR( ... , 0) like:
=IF(ISNUMBER(FIND("FWA",DATA!AM7)), "FWA", IF( ISNUMBER(FIND("JBER",DATA!AM7)),"JBER",""))
and I added a "" if not found

View solution in original post