IF statement searching for words in a cell

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I know how to write a basic "IF(OR" statement.

So, for example, if cell A2 had the text "Ball" or "Racket", and we wanted to return the word "Sports," then we could write this in cell B2 =IF(OR(A2="Ball",A2="Racket"),"Sports","")

But this is restrictive, because cell A2 can ONLY contain either "Ball" or "Racket" for the statement to return "Sports."

So, I'd like to use the =IF(ISNUMBER(SEARCH function, so that if a cell contains other words in it, but still includes the words "Ball" or "Racket" then the IF statement will still return the word "Sports."

I have the words "Club, Ball" in cell A1 and I've used =IF(ISNUMBER(SEARCH("Ball",A1)),"Sports","") to return the word "Sports"

Does anyone know how I can amend this, so that it STILL returns the word "Sports" if that cell also has the word "Racket"?

Thanks in advance.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(SUM(COUNTIFS(A2,{"*ball*","*Racket*"})),"Sports","")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You’re welcome.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,115
Messages
5,546,027
Members
410,721
Latest member
adi772
Top