IF(ISNUMBER(SEARCH with a 2nd criteria

[Deelof]

Hi, if column A contains "Core" and column B >=2 return 2 specific text conditions, "Good", "Need". What's the best formula?

 

[RoryA]

=IF(AND(ISNUMBER(SEARCH("CORE",A2)),B2>=2),"Good","Need")

should do it.

 

[Deelof]

=IF(AND(ISNUMBER(SEARCH("CORE",A2)),B2>=2),"Good","Need")

should do it.

Works a treat, thanks. How about if I want a 3rd condition adding as below example

 

[RoryA]

You've got overlap with the 1st and 3rd conditions as you've used >=2 and <=2 so both would include 2.

 

[Deelof]

You've got overlap with the 1st and 3rd conditions as you've used >=2 and <=2 so both would include 2.

So can a 3rd not be added, i.e. B is >=2 and C is >=5

 

[RoryA]

What I'm saying is that with the conditions as you have written them there, if B=2 and C>=5, the answer could be either Good or Need, so which should it be?

 

[Deelof]

What I'm saying is that with the conditions as you have written them there, if B=2 and C>=5, the answer could be either Good or Need, so which should it be?

Ah, maybe I haven't explained it well enough.
If B is equal or greater than 2 AND C is equal or greater than 5 then "Good"
If B is equal or greater than 2 AND C is equal or less than 5 then "Good"
If B is less than 2 AND C is equal or greater than 4 then "Need"
If B is less than 2 AND C is equal or less than 4 then "Not Required"

 

[RoryA]

Yeah, that is not what was in your picture!

Do we still need to check if Core is part of column A?

 

[Deelof]

Yeah, that is not what was in your picture!

Do we still need to check if Core is part of column A?

LOL, and no Core doesn't have to be factored in

 

[RoryA]

OK then. It would just be:

=IF(B2>=2,"Good",IF(C2>=4,"Need","Not required"))

although I just noticed that you introduced another overlap if column C is equal to 4:

If B is less than 2 AND C is equal or greater than 4 then "Need"
If B is less than 2 AND C is equal or less than 4 then "Not Required"