Highlighted
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

Highlighted

Re: Help with Formula

@Deleted

Hi Gail,

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

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

Highlighted

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”)