# search function with or function

#### winde

##### New Member
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.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Peter_SSs

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

#### JoeMo

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
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

##### MrExcel MVP
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 :banghead:

#### Peter_SSs

##### MrExcel MVP, Moderator
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.