search function with or function

winde

New Member
Joined
Nov 27, 2018
Messages
31
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.
 

Some videos you may like

Excel Facts

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,412
Office Version
365
Platform
Windows
Try
=IF(MIN(SEARCH({"Best","Good","Average"},A1&" BestGoodAverage",1))=1,"Pass","Fail")
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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
Joined
May 28, 2005
Messages
43,412
Office Version
365
Platform
Windows
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
Joined
May 26, 2009
Messages
16,692
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
Joined
May 28, 2005
Messages
43,412
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,089,225
Messages
5,406,955
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top