MS Excel 2013 SEARCH Function question 1

[puforee]

I am using a SEARCH function like this: =IF(ISNUMBER(SEARCH("LOS",$A6))=TRUE,$C6,"")

But, what I really want to do is to SEARCH for "LOS" OR "DTS" in the referenced cell. Can anyone show me how to format this?

Thanks,

John

 

[mintjulep]

Your cell will contain either "LOS" or "DTS", but never ever anything else?

You cell might contains things like "randomDTStext" or "unimportantLOSpart"?

Something else?

 

[kray4660]

Not sure of the syntax, but I think it would look something like this.

=IF(OR(ISNUMBER(SEARCH("LOS",$A6)),ISNUMBER(SEARCH("DTS",$A6)))=TRUE,$C6,"")

 

[combo]

COUNTIF is simpler:
=IF(COUNTIF(A6,"*LOS*")+COUNTIF(A6,"*DTS*")>0,C6,"")

combo

 

[kray4660]

Interesting solution!!

 

[puforee]

I thank you all for your responses. Another answer was staring me in the face. So, since I was using an IF statement I did this: =IF(ISNUMBER(SEARCH("Desktop Simulator",$J6))=TRUE,$K6,(IF(ISNUMBER(SEARCH("LOS",$J6))=TRUE,$K6,""))). It may be a little clunky but it works. I will be keeping your suggestions on file for future projects.

mintjulep the two terms are unique and they will be in a string of text.

Does anyone see a problem with my solution? Please let me know.

Thanks,

 

[combo]

It works, but the formula size grows significantly when you add new conditions. Moreover, it is hard to debug. ISNUMBER returns TRUE/FALSE, no need to add "=TRUE".
Presonally I would combine SEARCH results in single OR formula, without nested IFs, esp. due to common output ($K6 or ""):
[tt]=IF(OR(ISNUMBER(SEARCH("Desktop Simulator",$J6)),ISNUMBER(SEARCH("LOS",$J6))),$K6,"")[/tt]
New condition is simple in this case:
[tt]=IF(OR(ISNUMBER(SEARCH("Desktop Simulator",$J6)),ISNUMBER(SEARCH("LOS",$J6)),ISNUMBER(SEARCH("DTS",$J6))),$K6,"")[/tt]

combo