CODEWITHSUNDEEP
excelexcel-formula
David Garcia
David Garcia

Reputation: 2696

Excel: search multiple strings then return value

I am using the following formula to search for a string and return a value

IF(ISNUMBER(SEARCH("CHA*",B:B),"CINEMA","")

If I want to search for multiple strings whats the formula?, I've tried the following but it isn't returning anything

IF(ISNUMBER(SEARCH({"odeon*","vue*"},B:B),"CINEMA","")

Upvotes: 1

Views: 562

Answers (2)

watakushi
watakushi

Reputation: 51

For starters, you're missing a ) on either formula. And for them to work anyway, you should, as Taosique pointed out, use =IF(OR(ISNUMBER(SEARCH( if you need just one substring, or =IF(AND(ISNUMBER(SEARCH( if you require both.

Good luck!

Upvotes: 0

ttaaoossuu
ttaaoossuu

Reputation: 7884

Try this:

=IF(COUNT(SEARCH({"odeon*","vue*"},B:B))>0,"CINEMA","")

Another way:

=IF(OR(ISNUMBER(SEARCH({"odeon*","vue*"},B:B))),"CINEMA","")

Use AND() if you require ALL substrings to match:

=IF(AND(ISNUMBER(SEARCH({"odeon*","vue*"},B:B))),"CINEMA","")

Upvotes: 1

Related Questions