# How to nest more than two IF ISNUMBER SEARCH functions in a single cell formula?

Im trying to nest more than two (`ISNUMBER` `SEARCH`) statements in a cell and the last two don't work. The first two arguments ("n/a" & "r") work but the third and forth pull in the wrong data.

Can you have more than two searches in a cell? Where is my formula going wrong?

``````=IF(ISNUMBER(SEARCH("N/A",\$L2)),\$E2+\$AY\$2,IF(ISNUMBER(SEARCH("r",\$L2)),\$E2+\$AY\$3,IF(ISNUMBER(SEARCH("rr",\$L2)),\$E2+\$AY\$4,IF(ISNUMBER(SEARCH("rrr",\$L2)),\$E2+\$AY\$5))))
`````` • Your last IF have no ELSE section. Sep 30, 2019 at 6:53
• If constant part \$L2 must be presented always then use `=\$L2+IF(...)`. Sep 30, 2019 at 7:59
• Truly It's a good question,, formula fails due to technical reason which looks correct by logic,,, +1 ☺ Sep 30, 2019 at 10:21

It is caused because the formula finds single "r" in "rr" and also "rrr" cell values. Try to change order of ISNUMBER condition. Start with "rrr", then "rr" and finally "r". It should work.

``````=IF(ISNUMBER(SEARCH("N/A",\$L2)),\$E2+\$AY\$2,IF(ISNUMBER(SEARCH("rrr",\$L2)),\$E2+\$AY\$5,IF(ISNUMBER(SEARCH("rr",\$L2)),\$E2+\$AY\$4,IF(ISNUMBER(SEARCH("r",\$L2)),\$E2+\$AY\$3))))
``````

Best regards, Lukas

• ive got a feeling that the Akina answer above is correct and prob better but you answer works for me. Thanks v much Sep 30, 2019 at 7:50

Pretting the formula we see:

``````IF( ISNUMBER(SEARCH("N/A",\$L2)),
\$E2+\$AY\$2,
IF( ISNUMBER(SEARCH("r",\$L2)),
\$E2+\$AY\$3,
IF( ISNUMBER(SEARCH("rr",\$L2)),
\$E2+\$AY\$4,
IF( ISNUMBER(SEARCH("rrr",\$L2)),
\$E2+\$AY\$5
)
)
)
)
``````

Now we see:

1. Last (most inner) IF() do not contain ELSE section;
2. 2nd IF() gives True for any value tested by more inner IF() - so no inner IF() can give True because outer IF() have already did this.

Recommendation: replace SEARCH() with simple equation testing.

• Hi @Akina, the else section is not compulsory. My answer is correct and I do not understand why you marked it down. Best regards, Lukas.
– Muji
Sep 30, 2019 at 7:26
• @Muji I do not understand why you marked it down. But I do not understand why you decide that it is my downvote. That is not true. Search elsewhere. the else section is not compulsory. OP asks what is wrong in the formula. I think the ELSE section absence is wrong, and I tell about it. OP may think it is safe and ignore. Sep 30, 2019 at 7:34
• sorry for my accusation. Else section is not really needed, try to formula without it. I absolutely agree with you it should be there, because it is safe and it prevents of error statuses.
– Muji
Sep 30, 2019 at 7:37
• @Muji Yes, this section is optional, but when it is absent then boolean FALSE value is generated. I am not sure it is correct. All another variants generates `\$E2+something` value, so constant part always present, whereas skipped variant will produce the boolean FALSE value or, if destination cell is numbering-formatted, zero, without this constant part. Sep 30, 2019 at 7:44
• im not sure what 'replace SEARCH() with simple equation testing' means. But thanks for you help Sep 30, 2019 at 7:51

The `SEARCH` with the `ISNUMBER`function, can be used to check, if the cell contains specific text or not.

`SEARCH` returns the position of the search string if found, and the formula tests whether the search string is found, by using the `ISNUMBER` to find it's numeric positions.

In your case while finding `rr` or `rrr`, formula finds the `r` among `rr` and in `rrr` and every time calculates `\$E2+\$AY\$3` instead of `\$E2+\$AY\$4` or `\$E2+\$AY\$5`.

If `L2` contains any text other than `rr` or `rrr` the same formula will work.

@Muji did the trick, used search strings in reverse order so that the formula finds 1st the `rrr` then `rr` & `r` in `rrr` and works properly.

The simple `IF function` can test all these successfully.

``````=IF(\$L\$2="N/A",\$E\$2+\$AY\$2,IF(\$L\$2="r",\$E\$2+\$AY\$3,IF(\$L\$2="rr",\$E\$2+\$AY\$4,IF(\$L\$2="rrr",\$E\$2+\$AY\$5))))
``````

Adjust cell references in the formula as needed.

• That is true :-)
– Muji
Sep 30, 2019 at 10:51