1

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

My Spreadsheet

Thanks for your help in advance

Improve this question
3
  • Your last IF have no ELSE section.
    – Akina
    Sep 30, 2019 at 6:53
  • If constant part $L2 must be presented always then use =$L2+IF(...).
    – Akina
    Sep 30, 2019 at 7:59
  • Truly It's a good question,, formula fails due to technical reason which looks correct by logic,,, +1 ☺
    – Rajesh Sinha
    Sep 30, 2019 at 10:21

3 Answers 3

Reset to default
0

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

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

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.

Improve this answer
6
  • 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.
    – Akina
    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
  • 1
    @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.
    – Akina
    Sep 30, 2019 at 7:44
  • im not sure what 'replace SEARCH() with simple equation testing' means. But thanks for you help
    – Betty
    Sep 30, 2019 at 7:51
1

The SEARCH with the ISNUMBERfunction, 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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .