Forum Discussion
Getting an error when using nested IF function
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.
- 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
- 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.
