Home

Help with Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-687222%22%20slang%3D%22en-US%22%3EHelp%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687222%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20to%20return%20a%20value%20if%20a%20cell%20includes%20a%20word(s)%20within%20the%20cell%20being%20evaluated.%26nbsp%3B%20For%20example%2C%20the%20cell%20contains%20the%20words%20%22Summit%20Ad%20Hoc%20Hourly%22.%26nbsp%3B%20I%20want%20to%20evaluate%20if%20the%20cell%20includes%20the%20words%20Ad%20Hoc%2C%20and%20if%20so%2C%20return%20Ad%20Hoc%2C%20otherwise%2C%20return%20Core.%26nbsp%3B%20The%20formula%20works%20in%20that%20I%20can%20get%20it%20to%20evaluate%20the%20cell%2C%20however%2C%20it%20is%20not%20recognizing%20%22Ad%20Hoc%22%20and%20is%20returning%20the%20value%20for%20false.%26nbsp%3B%20Here's%20what%20I%20have%20thus%20far%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(K2%3D%22Ad%22%26amp%3B%22Hoc%22%2C%22Ad%20Hoc%22%2C%22Core%22).%26nbsp%3B%20The%20formula%20should%20return%20a%20value%20of%20%22Ad%20Hoc%22%20to%20the%20cell%20but%20instead%20is%20returning%20%22Core%22.%26nbsp%3B%20I'm%20sure%20it%20is%20because%20the%20formula%20is%20looking%20for%20only%20%22Ad%20Hoc%22%20as%20the%20full%20value%20within%20the%20cell%2C%20however%2C%20there%20are%20also%20other%20words%20in%20that%20cell%2C%20i.e.%2C%20Summit%20Ad%20Hoc%20Hourly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20makes%20sense!%26nbsp%3B%20Any%20guidance%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3EGail%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-687222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687321%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687321%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20understand%20this%20properly%2C%20this%20formula%20might%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(K2%3D%22Ad%22%26amp%3B%22Hoc%22%2C%22Ad%20Hoc%22%2C%20IF(K2%3D%20%22Ad%22%20%26amp%3B%20%22Hoc%22%2C%22Core%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShaina%20B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687543%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358945%22%20target%3D%22_blank%22%3E%40Gail_Miller%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Gail%2C%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISNUMBER(SEARCH(%22Ad%20Hoc%22%2CK2))%2C%22Ad%20Hoc%22%2C%22Core%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688250%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20thank%20you%20so%20much!%26nbsp%3B%20That%20worked!%26nbsp%3B%20Now%2C%20I%20need%20to%20take%20it%20a%20step%20further%20and%20search%20for%20%22ad%20hoc%22%20across%20a%20range%20of%20cells%2C%20J2%3AAT2%20and%20if%20not%20found%20insert%20%22core%22%20into%20the%20cell.%26nbsp%3B%20Can%20you%20help%20me%20with%20that%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EGail%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688307%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688307%22%20slang%3D%22en-US%22%3EYou%20may%20COUNTIF%20any%20of%20the%20cells%20in%20the%20range%20contains%20%E2%80%9CAd%20Hoc%E2%80%9D%2C%20like%20this%3A%3CBR%20%2F%3E%3DIF(COUNTIF(J2%3AAT2%2C%E2%80%9D*Ad%20Hoc*%E2%80%9D)%2C%3CBR%20%2F%3E%E2%80%9CAd%20Hoc%E2%80%9D%2C%E2%80%9DCore%E2%80%9D)%3C%2FLINGO-BODY%3E
Deleted
Not applicable

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

If I understand this properly, this formula might work.

 

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

 

Shaina B

Highlighted

@Deleted 

 

Hi Gail,

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

@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

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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies