Using OR in SEARCH String

[Whylucky]

Hi All,

So i wrote an excel equation to spit out a date based on 2 criteria. If it has a 1, 2, or 4 in one column then an A, B, or C in the next column it gives a result. Then if it has a 5, 6, or 7 in another column and a A, B, or C it gives a certain result. I have the equation done other than i need a way to search for 1, 2 or 4 and i can not find a way to search multiple options. I highlighted the section that needs to be adjusted, does anyone have any suggestions?

P.S. In the first searched column numbers are not the only thing contained in the searched cells

EQUATION:

=IF(IFERROR([COLOR=#ff0000]SEARCH("1",$Q2),0)[/COLOR],IF(IFERROR(SEARCH("A=",$P2),0),DATE(YEAR(S2),MONTH(S2),DAY(S2)-20),IF(IFERROR(SEARCH("B=",$P2),0),DATE(YEAR(S2),MONTH(S2),DAY(S2)-25),IF(IFERROR(SEARCH("C=",$P2),0),DATE(YEAR(S2),MONTH(S2),DAY(S2)-80),"-"))),IF(IFERROR([COLOR=#ff0000]SEARCH("5",$Q2),0)[/COLOR],IF(IFERROR(SEARCH("A=",$P2),0),DATE(YEAR(S2),MONTH(S2),DAY(S2)-49),IF(IFERROR(SEARCH("B=",$P2),0),DATE(YEAR(S2),MONTH(S2),DAY(S2)-61),IF(IFERROR(SEARCH("C=",$P2),0),DATE(YEAR(S2),MONTH(S2),DAY(S2)-80),"-"))),"-"))

 

[Andrew Poulsom]

A couple of possibilities:

SUM(COUNTIF($Q2,{"*1*","*2*","*4*"}))

SUM(--ISNUMBER(SEARCH({"1","2","4"},$Q2)))

 

[Whylucky]

Thanks I used the first option, i tried to do a SEARCH({"1","2","3"},$Q2) and it wouldnt return a value is there a reason for this?

 

[Andrew Poulsom]

That formula will only work for the first value in the array.

 

[Whylucky]

Yea i noticed that when i was typing it up, what would it take to make that work, for future reference?

 

[Andrew Poulsom]

What do you want to do with the results?

 

[Whylucky]

it was for the same purpose above, choosing a case to change the date a certain amount

 

[Andrew Poulsom]

In that case use the formula I posted. It tests that SEARCH succeeds and SUMs the number of successes.