SOLVED

searching a sheet with a true and false function

%3CLINGO-SUB%20id%3D%22lingo-sub-1197104%22%20slang%3D%22en-US%22%3Esearching%20a%20sheet%20with%20a%20true%20and%20false%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197104%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20trying%20to%20search%20a%20column%20to%20give%20me%20a%20true%20or%20false%20value%20in%20the%20column%20next%20to%20it.%20I%20do%20not%20know%20how%20to%20write%20the%20formula%20I%20need.%20I%20would%20like%20to%20search%20column%20B%20row%202%20through%2010.%20If%20the%20cell%20contains%20the%20word%20'pencil'%20and%20excludes%20the%20words%20'case'%2C%20'grip'%2C%20'sharpener'%2C%20and%20'mechanical'%20I%20would%20like%20the%20corresponding%20cell%20in%20column%20C%20to%20read%20TRUE.%20Is%20there%20a%20formula%20I%20could%20use%20for%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1197104%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197167%22%20slang%3D%22en-US%22%3ERe%3A%20searching%20a%20sheet%20with%20a%20true%20and%20false%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197167%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568981%22%20target%3D%22_blank%22%3E%40Aaron_excel%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERefer%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcell-contains-some-words-but-not-others%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fcell-contains-some-words-but-not-others%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197265%22%20slang%3D%22en-US%22%3ERe%3A%20searching%20a%20sheet%20with%20a%20true%20and%20false%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197265%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20My%20google%20searches%20gave%20me%20everything%20but%20hard%20coding%20text.%20Saved%20my%20bacon!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197293%22%20slang%3D%22en-US%22%3ERe%3A%20searching%20a%20sheet%20with%20a%20true%20and%20false%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568981%22%20target%3D%22_blank%22%3E%40Aaron_excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGlad%20to%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197373%22%20slang%3D%22en-US%22%3ERe%3A%20searching%20a%20sheet%20with%20a%20true%20and%20false%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197373%22%20slang%3D%22en-US%22%3E%3CP%3E%3DAND(COUNT(SEARCH(%7B%22*pencil*%22%7D%2CB2))%26gt%3B0%2CCOUNT(SEARCH(%7B%22*case*%22%2C%22*color*%22%2C%22*colored*%22%2C%22*grip*%22%2C%22*eraser*%22%2C%22*mechanical*%22%2C%22*sharpener*%22%7D%2CB2))%3D0)%3C%2FP%3E%3CP%3EThe%20above%20formula%20is%20what%20I%20created%20giving%20me%20a%20true%20or%20false%20in%20the%20cell.%20Is%20there%20a%20way%20to%20count%20how%20many%20came%20back%20true%3F%20Also%20Can%20I%20search%20just%20for%20the%20cells%20that%20came%20back%20true%3F%20I%20am%20trying%20to%20navigate%20with%20thousands%20of%20rows.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197524%22%20slang%3D%22en-US%22%3ERe%3A%20searching%20a%20sheet%20with%20a%20true%20and%20false%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568981%22%20target%3D%22_blank%22%3E%40Aaron_excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20help%20of%20column%20C%2C%20the%20count%20of%20TRUE%20could%20simply%20be%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTIF(C2%3AC10%2CTRUE)%3C%2FPRE%3E%3CP%3EWithout%20the%20help%20of%20column%20C%2C%20that%20could%20be%3A%3C%2FP%3E%3CPRE%3E%26nbsp%3B%3DCOUNT(SEARCH(%7B%22*pencil*%22%7D%2CB2%3AB10))-COUNT(SEARCH(%7B%22*case*%22%2C%22*color*%22%2C%22*colored*%22%2C%22*grip*%22%2C%22*eraser*%22%2C%22*mechanical*%22%2C%22*sharpener*%22%7D%2CB2%3AB10))%7D%3C%2FPRE%3E%3CP%3ENote%3A%20I%20would%20recommend%20creating%20a%20table%20with%20included%2Fexcluded%20strings%20as%20the%20list%20of%20excluded%20could%20easily%20get%20quite%20lengthy.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, I am trying to search a column to give me a true or false value in the column next to it. I do not know how to write the formula I need. I would like to search column B row 2 through 10. If the cell contains the word 'pencil' and excludes the words 'case', 'grip', 'sharpener', and 'mechanical' I would like the corresponding cell in column C to read TRUE. Is there a formula I could use for this? 

5 Replies
Highlighted
Highlighted

Thank you so much! My google searches gave me everything but hard coding text. Saved my bacon!@PReagan 

Highlighted
Highlighted

=AND(COUNT(SEARCH({"*pencil*"},B2))>0,COUNT(SEARCH({"*case*","*color*","*colored*","*grip*","*eraser*","*mechanical*","*sharpener*"},B2))=0)

The above formula is what I created giving me a true or false in the cell. Is there a way to count how many came back true? Also Can I search just for the cells that came back true? I am trying to navigate with thousands of rows. @PReagan 

Highlighted

@Aaron_excel 

 

With the help of column C, the count of TRUE could simply be:

=COUNTIF(C2:C10,TRUE)

Without the help of column C, that could be:

 =COUNT(SEARCH({"*pencil*"},B2:B10))-COUNT(SEARCH({"*case*","*color*","*colored*","*grip*","*eraser*","*mechanical*","*sharpener*"},B2:B10))}

Note: I would recommend creating a table with included/excluded strings as the list of excluded could easily get quite lengthy.