search function with or function

[winde]

how do you incorporate the search & or function into the same formula?

i keep getting #Value with my formula. Any help is appreciated.

=if(search(or("Best","Good","Average"),A1,1)=1,"Pass","Fail")

Thanks.

 

[Peter_SSs]

Try
=IF(MIN(SEARCH({"Best","Good","Average"},A1&" BestGoodAverage",1))=1,"Pass","Fail")

 

[JoeMo]

SEARCH doesn't handle an array in its first argument. Here are a couple of alternatives provided A1 will always be a text value.
=IF(LOOKUP(A1,{"Average","Best","Good","*"},{1,1,1,0}),"Pass","Fail")
=LOOKUP(A1,{"Average","Best","Good","*"},{"Pass","Pass","Pass","Fail"})

Note that the first array must be arranged in ascending order.

 

[Peter_SSs]

SEARCH doesn't handle an array in its first argument.

I disagree. Did you try my formula?

BTW, if A1 contained "Fail" or "Bad score" your suggested formulas return "Pass"

 

[JoeMo]

I disagree. Did you try my formula?

BTW, if A1 contained "Fail" or "Bad score" your suggested formulas return "Pass"

What I meant was SEARCH alone doesn't handle array arguments. Your formula is clever. Isn't it depending on the MIN function to process the array?

...and yes my formula fails miserably. Should have tested it

 

[Peter_SSs]

What I meant was SEARCH alone doesn't handle array arguments.

Agreed.

Isn't it depending on the MIN function to process the array?

Yes, SEARCH does require a little "arm twisting" before it will process the array - but at least it will in that circumstance.