• 507K Members
• 6,027 Online
• 603K Conversations

Deleted
Not applicable

# Help with Formula

I am trying to create a formula to return a value if a cell includes a word(s) within the cell being evaluated.  For example, the cell contains the words "Summit Ad Hoc Hourly".  I want to evaluate if the cell includes the words Ad Hoc, and if so, return Ad Hoc, otherwise, return Core.  The formula works in that I can get it to evaluate the cell, however, it is not recognizing "Ad Hoc" and is returning the value for false.  Here's what I have thus far:

=IF(K2="Ad"&"Hoc","Ad Hoc","Core").  The formula should return a value of "Ad Hoc" to the cell but instead is returning "Core".  I'm sure it is because the formula is looking for only "Ad Hoc" as the full value within the cell, however, there are also other words in that cell, i.e., Summit Ad Hoc Hourly.

Hopefully this makes sense!  Any guidance would be appreciated.

Sincerely,

Gail

4 Replies
Highlighted

# Re: Help with Formula

If I understand this properly, this formula might work.

IF(K2="Ad"&"Hoc","Ad Hoc", IF(K2= "Ad" & "Hoc","Core"))

Shaina B

# Re: Help with Formula

@Deleted

Hi Gail,

`=IF(ISNUMBER(SEARCH("Ad Hoc",K2)),"Ad Hoc","Core")`

# Re: Help with Formula

@Sergei Baklan, thank you so much!  That worked!  Now, I need to take it a step further and search for "ad hoc" across a range of cells, J2:AT2 and if not found insert "core" into the cell.  Can you help me with that?

Thank you!

Gail

# Re: Help with Formula

You may COUNTIF any of the cells in the range contains “Ad Hoc”, like this:
=IF(COUNTIF(J2:AT2,”*Ad Hoc*”),
“Ad Hoc”,”Core”)
Related Conversations
Need help with concatenate formula
Dave Cunningham in Excel on
2 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies