Combine ISNUMBER SEARCH with AND function

[Craig__]

Hi guys

I just can’t seem to work this one out. Your help would be most appreciated.

Is it possible to combine the AND function with the following formula?

=IF(ISNUMBER(SEARCH("high",A5)),1,IF(ISNUMBER(SEARCH("moderate",A5)),1,""))

Something like this:
=IF(AND(ISNUMBER(SEARCH("high",A5)),1,IF(ISNUMBER(SEARCH("moderate",A5)),C5>"",1,"")))

What I want is a formula that says:
If A5 is found to contain the word high or moderate, and C5 is not empty, then display a value of 1, otherwise display nothing.

 

[Aladin Akyurek]

thank you so much, just need to find out how to keyword search one of the reference boxes as it contains extra info. im sure you use the SEARCH function but not sure on these array things

dave

Do you mean N3 and P3? Care to provide some examples?

 

[alienkidmj12]

Ok and Rotten would also have in there boxes

Ok:Fruit
Rotten:Fruit

So i need to search the boxes for only Ok or Rotten

So i need exactly the same, just ignoring the :Fruit bit

thanks

dave

 

[T. Valko]

Ok and Rotten would also have in there boxes

Ok:Fruit
Rotten:Fruit

So i need to search the boxes for only Ok or Rotten

So i need exactly the same, just ignoring the :Fruit bit

thanks

dave

Can you post some sample data and tell us what results you expect?

 

[Aladin Akyurek]

Ok and Rotten would also have in there boxes

Ok:Fruit
Rotten:Fruit

So i need to search the boxes for only Ok or Rotten

So i need exactly the same, just ignoring the :Fruit bit

thanks

dave

Control+shift+enter, not just enter:

Either...

=INDEX($C$2:$C$5,MATCH(LEFT(N3,FIND(":",N3)-1),
    IF($A$2:$A$5=P3,$B$2:$B$5),0))

Or...

=INDEX({"Eat 1";"Don't eat 1";"Eat 2";"Don't eat 2"},
   MATCH(LEFT(N3,FIND(":",N3)-1),
     IF({"Bananna";"Bananna";"Apple";"Apple"}=P3,
        {"Ok";"Rotten";"Ok";"Rotten"}),0))