SOLVED

HOW TO: "If cell contains specific text then return specific text"

%3CLINGO-SUB%20id%3D%22lingo-sub-69655%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69655%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%26nbsp%3Bextract%20a%20bunch%20of%20specific%20text%20combinations%20from%20cells%20and%20present%20it%20in%20a%20new%20cell.%3C%2FP%3E%3CP%3EThis%20formula%20seems%20to%20work%20for%20two%20variables%20but%20I%20can't%20add%20any%20more%20variables%20too%20it.%3C%2FP%3E%3CP%3E%3DIFERROR(IF(SEARCH(%22*Sales*%22%2CB3%2C1)%2C%22Sales%22)%2CIF(SEARCH(%22*Arch*%22%2CB3%2C1)%2C%22Architecture%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20text%20I%20would%20be%20searching%20for%20would%20be%3A%3C%2FP%3E%3CP%3ESales%2C%3C%2FP%3E%3CP%3EArch%2C%3C%2FP%3E%3CP%3ELand%2C%3C%2FP%3E%3CP%3EALL%2C%3C%2FP%3E%3CP%3EContracts%2C%3C%2FP%3E%3CP%3EConstruction%3C%2FP%3E%3CP%3Eand%20possibly%20a%20couple%20more.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%26nbsp%3Bdo%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-69655%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-70337%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-70337%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Mr.%20Vijaykumar%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20and%20appreciate%20your%20support.%3C%2FP%3E%3CP%3EI%20am%20beginner%26nbsp%3Band%20I%20am%20always%26nbsp%3Bwelcome%20one%20who%20want%20to%20give%20valuable%20suggestions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20with%20best%20regards%2C%3C%2FP%3E%3CP%3EManoj.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-70335%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-70335%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Mr.%20Sergi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes.%20You%20are%20absolutely%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CIT%20is%3D%22%22%20worth%3D%22%22%20to%3D%22%22%20re-read%3D%22%22%20the%3D%22%22%20question%3D%22%22%3E%3C%2FIT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20beginner%26nbsp%3Bin%20excel%20and%20I%20am%20always%20welcome%20one%20who%20gives%20valuable%20suggestion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20best%20regards%2C%3C%2FP%3E%3CP%3EManoj.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69802%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69802%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20sorry%20but%20i%20see%20no%20limitations%20which%20could%20prevent%20to%20use%20nested%20IF.%20The%20only%20point%20it%20is%20a%20bit%20less%20flexible%20compare%20to%20nested%20If%20equivalents.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20what%20to%20use%20depends%20on%20goals%2C%20in%20some%20cases%20quick%20hardcording%20works%20quite%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20use%20formatting%20nested%20if%20becomes%20much%20more%20clear%20and%20editable.%20Like%20this%3C%2FP%3E%3CPRE%3E%3DIF(ISNUMBER(SEARCH(%22Sales%22%2C%20%20%20%20%20%20%20%20B3%2C1))%2C%22Sales%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22Arch%22%2C%20%20%20%20%20%20%20%20%20B3%2C1))%2C%22Architecture%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22Land%22%2C%20%20%20%20%20%20%20%20%20B3%2C1))%2C%22Land%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22All%22%2C%20%20%20%20%20%20%20%20%20%20B3%2C1))%2C%22All%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22Contracts%22%2C%20%20%20%20B3%2C1))%2C%22Contracts%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22Construction%22%2C%20B3%2C1))%2C%22Construction%22%2C%3CBR%20%2F%3E%20%22No%20Match%22%3CBR%20%2F%3E%20))))))%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%3C%2FPRE%3E%3CP%3EIf%20instead%20of%20hardcoded%20strings%20use%20references%20nested%20IF%26nbsp%3Bbecomes%20more%20flexible.%20And%20if%20add%20some%20extra%20references%20(nested%20if)%20for%20future%20strings%20to%20find%20it%20becomes%20even%20more%20flexible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%20%3DIF(ISNUMBER(SEARCH(%24F%241%2CB3%2C1))%2C%24G%241%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%242%2CB3%2C1))%2C%24G%242%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%243%2CB3%2C1))%2C%24G%243%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%244%2CB3%2C1))%2C%24G%244%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%245%2CB3%2C1))%2C%24G%245%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%246%2CB3%2C1))%2C%24G%246%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%247%2CB3%2C1))%2C%24G%247%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%248%2CB3%2C1))%2C%24G%248%2C%0A%20%20IF(ISNUMBER(SEARCH(%24F%249%2CB3%2C1))%2C%24G%249%2C%0A%20%20%20%20%22No%20Match%22%0A%20)))))))))%0A%20%3C%2FPRE%3E%3CP%3EI%20don't%20vote%20for%26nbsp%3Bnested%20IF%2C%20%26nbsp%3Bi%20would%20like%20to%20say%20where%20is%20no%20limitations%20here.%20What%20to%20use%20that's%20concrete%20person%20choice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69797%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69797%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Manoj%20Patgar%2C%3C%2FP%3E%3CP%3E(1)%26nbsp%3B%20The%20problem%20has%20been%20posted%20on%20the%20forum%20by%20Reuben%20Helder%20because%20the%20formula%20using%20nested%20If%20functions%20is%20not%20adequate%2C%20due%20to%20its%20limitations.%3C%2FP%3E%3CP%3EThe%20number%20of%20items%20in%20the%20text%20list%20is%20large%20(Sales%2C%20Arch%2C%20Land%2C%20ALL%2C%20Contracts%2C%20Construction%20and%20possibly%20a%20couple%20more)%2C%20which%20nested%20IFs%20cannot%20handle.%20So%20the%20same%20formula%20with%20minor%20changes%20cannot%20work.%20The%20formula%20to%20be%20used%20has%20to%20be%20disigned%20to%20handle%20a%20long%20list%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(2)%26nbsp%3B%20The%20data%20which%20is%20being%20searched%20is%20not%20%3CSTRONG%3EMECH%3C%2FSTRONG%3E%20or%20%3CSTRONG%3EARCH%3C%2FSTRONG%3E.%20It%20will%20be%20like%20Searching%20%3CSTRONG%3EMECH%3C%2FSTRONG%3E%20or%20%3CSTRONG%3EARCH%3C%2FSTRONG%3E%20within%20a%20data%20list%20that%20contain%20entries%20like%20'ABC%20%3CSTRONG%3EMECH%3C%2FSTRONG%3EANICAL%20ENGINEERS'%20or%20'XYX%20%3CSTRONG%3EARCH%3C%2FSTRONG%3EITECTS'.%20Hence%20the%20wild%20card%20character%20(%3CSTRONG%3E*%3C%2FSTRONG%3E)%20has%20been%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20clear%20now%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVijaykumar%20Shetye%2C%3C%2FP%3E%3CP%3ESpreadsheet%20Excellence%2C%3C%2FP%3E%3CP%3EPanaji%2C%20Goa%20India%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69779%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Manoj%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20worth%20to%20re-read%20task%20requirements%20and%20previous%20posts%2C%20your%20formula%20is%20about%20something%20different.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69778%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69778%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CSPAN%20class%3D%22%22%3E%26nbsp%3BReuben%20Helder%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EGood%20day%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EPlease%20refer%20below%20formula%20to%20add%20more%20variable.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EIf%20statment%20is%20false%20then%20I%20have%20considered%20criteria%20is%20%22NO%20MATCH%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EIF(B3%3D%22MECH%22%2C%22MECHANICAL%22%2CIF(B3%3D%22ARCH%22%2C%22ARCHITECTURE%22%2CIF(B3%3D%22SALES%22%2C%22SALES%22%2CIF(B3%3D%22ELE%22%2C%22ELECTRICAL%22%2C%22NO%20MATCH%22))))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EIs%20this%20helful%20for%20you%3F%20please%20revert%20bach.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThanks%20%26amp%3B%20regards%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EManoj%20P.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69771%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69771%22%20slang%3D%22en-US%22%3E%3CP%3EPI()%20itself%20is%20just%20a%20little%20gag.%20The%20lookup%20value%20has%20to%20be%20a%20number%20greater%20than%201.%20So%201.1%20would%20be%20enough%20or%202%20if%20you%20prefer%20whole%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69766%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69766%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Detlef%20Lewin%2C%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%20and%20the%20correction.%20What%20is%20the%20significance%20of%20using%20Pi()%20in%20the%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EViaykumar%20Shetye%2C%3C%2FP%3E%3CP%3ESpreadsheet%20Excellence%2C%3C%2FP%3E%3CP%3EPanaji%2C%20Goa%2C%20India%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69750%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69750%22%20slang%3D%22en-US%22%3E%3CP%3EVijaykumar%20Shetye%2C%20my%20formula%20does%20not%20need%20amending.%3C%2FP%3E%3CP%3EAnd%20your%20change%20from%20PI()%20to%201%20will%20possibly%20give%20wrong%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69744%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69744%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Reuben%20Helder%2C%3CBR%20%2F%3E%3CBR%20%2F%3EKindly%20note%20that%20the%20below%20formula%20which%20I%20had%20given%20in%20the%20provious%20post%2C%20is%20an%20%3CSTRONG%3Earray%20formula%3C%2FSTRONG%3E.%3CBR%20%2F%3E%3CSTRONG%3E%3DINDEX(%24F%241%3A%24F%246%2CSUM(COUNTIF(%24A1%2C%22*%22%26amp%3B%24F%241%3A%24F%246%26amp%3B%22*%22)*ROW(%24F%241%3A%24F%246)))%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3EArray%20formulas%20are%20entered%20using%20%3CSTRONG%3EControl%2BShift%2BEnter%3C%2FSTRONG%3E%20instead%20of%20Enter.%3CBR%20%2F%3E%3CBR%20%2F%3EVijaykumar%20Shetye%3CBR%20%2F%3ESpreadsheet%20Excellence%2C%3CBR%20%2F%3EPanaji%2C%20Goa%2C%20India%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69741%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69741%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Reuben%20Helder%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20may%20also%20use%20the%20below%20formula%20for%20the%20task.%20I%20have%20retained%20the%20same%20cell%20references%20that%20have%20been%20used%20by%20Detlef%20Lewin%20in%20the%20solution%20that%20he%20has%20provided.%3CBR%20%2F%3E%3CSTRONG%3E%3DINDEX(%24F%241%3A%24F%246%2CSUM(COUNTIF(%24A1%2C%22*%22%26amp%3B%24F%241%3A%24F%246%26amp%3B%22*%22)*ROW(F1%3AF6)))%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20formula%20given%20by%20Detlef%20Lewin%20can%20be%20amended%2C%20so%20that%20Pi()%20and%20column%20G%20will%20not%20be%20required.%3CBR%20%2F%3E%3CSTRONG%3E%3DLOOKUP(1%2C1%2FCOUNTIF(%24A1%2C%22*%22%26amp%3B%24F%241%3A%24F%246%26amp%3B%22*%22)%2C%24F%241%3A%24F%246)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3EVijaykumar%20Shetye%3CBR%20%2F%3ESpreadsheet%20Excellence%2C%3CBR%20%2F%3EPanaji%2C%20Goa%2C%20India%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69683%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69683%22%20slang%3D%22en-US%22%3E%3CP%3EAh%2C%20a%20classic%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKey%20words%20in%20column%20F%2C%20returned%20text%20in%20column%20G%2C%20text%20to%20check%20in%20A1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DLOOKUP(PI()%2C1%2FCOUNTIF(A1%2C%22*%22%26amp%3B%24F%241%3A%24F%246%26amp%3B%22*%22)%2C%24G%241%3A%24G%246)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-440439%22%20slang%3D%22en-US%22%3EUsing%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-440439%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I'm%20looking%20to%20create%20a%20formula%20to%20calculate%20how%20much%20I%20spend%20on%20groceries%20a%20year.%20For%20an%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(B1%3C%2FFONT%3E%3CFONT%3E%3D%22nofrills%22%2C%22Loblaws%22%2C%22sobeys%22%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ethen%20display%20C1%20(the%20%24%24)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eotherwise%20false%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-443541%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-443541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319636%22%20target%3D%22_blank%22%3E%40scmallory%3C%2FA%3E%26nbsp%3B%2C%20that's%20a%20separate%20question%2C%20better%20to%20start%20new%20conversation%20with%20it%20from%20here.%3C%2FP%3E%0A%3CP%3EFormula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUM(--(B1%3D%7B%3CSPAN%3E%22nofrills%22%2C%22Loblaws%22%2C%22sobeys%22%3C%2FSPAN%3E%7D))%2CC1)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-444620%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-444620%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%3EI'm%20having%20troubles%20posting%20in%20a%20new%20thread.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tested%20out%20the%20formula%20and%20I%20was%20hoping%20cells%20in%20column%20D%20it%20would%20spit%20out%20the%20value%20in%20column%20C%20for%20anywhere%20it%20says%20%22Food%20Basic%22%20in%20column%20B.%20So%20the%20attached%20Test%20document.%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-445426%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-445426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319636%22%20target%3D%22_blank%22%3E%40scmallory%3C%2FA%3E%26nbsp%3B%2C%20to%20start%20new%20conversation%20go%20to%20the%20General%20Discussion%20as%20in%20my%20link%20and%20click%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20479px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108434i032FCC66822AFB4C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20doesn't%20work%20since%20in%20column%20B%20you%20have%20%22food%20basics%20%22%2C%20and%20you%20compare%20with%26nbsp%3B%22food%20basics%22.%20Due%20to%20space%20texts%20are%20different.%20Trim%20the%20cell%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUM(--(TRIM(B1)%3D%7B%22nofrills%22%2C%22food%20basics%22%2C%22Loblaws%22%2C%22sobeys%22%7D))%2CC1)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-446086%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-446086%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%3EThanks%20it%20worked!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-446127%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-446127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319636%22%20target%3D%22_blank%22%3E%40scmallory%3C%2FA%3E%26nbsp%3B%2C%20good%2C%20thank%20you%20for%20the%20confirmation%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624401%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624401%22%20slang%3D%22en-US%22%3ENot%20OP%2C%20but%20this%20was%20exactly%20what%20I%20was%20looking%20for%2C%20thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-650706%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650706%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%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20to%20do%20the%20following.%20I%20believe%20it%20is%20an%20%22IF%22%2C%20but%20not%20sure.%20I%20basically%20want%20a%20formula%20to%20return%20results%20based%20on%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20cell%20with%20the%20number%20is%20%26lt%3B%3D50%2C%20then%20multiply%20by%202%3C%2FP%3E%3CP%3EIf%20the%20cell%20with%20the%20number%20is%20%26gt%3B50%2C%20but%20%26lt%3B%3D100%2C%20then%20multiply%20by%201.75%3C%2FP%3E%3CP%3EIf%20the%20cell%20with%20the%20number%20is%20%26gt%3B100%2C%20but%20%26lt%3B%3D200%2C%20then%20multiply%20by%201.50%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20combine%20in%20one%20formula%20so%20that%20I%20can%20copy%20it%20all%20the%20way%20down%20the%20spreadsheet.%20Any%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808020%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808020%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much%20-%20I%20spent%20so%20long%20looking%20for%20this%20-%20it%20works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808058%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808058%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DA1*IF(A1%26gt%3B200%2C1%2CIF(A1%26gt%3B100%2C1.5%2CIF(A1%26gt%3B50%2C1.75%2C2)))%0Aor%0A%3DA1*LOOKUP(A1%2C%7B0%2C50.000001%2C100.000001%2C200.000001%7D%2C%7B2%2C1.75%2C1.5%2C1%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349610%22%20target%3D%22_blank%22%3E%40bengal1022%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-958263%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958263%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%26nbsp%3Bthis%20is%20exactly%20what%20I%60m%20looking%20for%20but%20excel%20gives%20me%20an%20error%20-%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F151469i2F23D4631C0B57B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.JPG%22%20title%3D%221.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1083613%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BThis%20works%20perfectly%20in%20most%20situations%20and%20is%20super%20simple%2C%20so%20I%20want%20to%20thank%20you%20for%20providing%20this!%20I%20haven't%20seen%20this%20solution%20offered%20anywhere%20else!%20The%20only%20problem%20I%20sometimes%20run%20into%20is%20that%20this%20isn't%20an%20exact%20match%2C%20so%20if%20I%20have%20a%20list%20of%20text%20that%20has%20some%20of%20the%20same%20terms%20or%20letter%20combinations%2C%20it%20sometimes%20returns%20the%20wrong%20thing.%20Is%20there%20a%20way%20to%20make%20this%20exact%20match%20only%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1083647%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3DIF%20formula%20to%20calculate%20spending%20on%20groceries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083647%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F434576%22%20target%3D%22_blank%22%3E%40krisi042%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20attache%20the%20file%20instead%20of%20screenshort.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1166160%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1166160%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%26nbsp%3BI%20really%20like%20your%20solution%20with%20nested%20formatting%2C%20though%20I%20was%20wondering%20if%20there%20is%20a%20way%20to%20efficiently%20do%20a%20few%20hundred%20words%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20must%20be%20a%20better%20option%20than%20typing%20in%20all%20the%20words%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1166848%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1166848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F553134%22%20target%3D%22_blank%22%3E%40Freek_Olivier%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20gave%20nested%20IF%20only%20to%20illustrate%20that%20is%20workable.%20Formula%20which%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bsuggested%20shall%20work%20perfectly%2C%20especially%20on%20big%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318951%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F44810%22%20target%3D%22_blank%22%3E%40Reuben%20Helder%3C%2FA%3E%26nbsp%3BI%20am%20working%20on%20a%20spreadsheet%20that%20contains%20macros.%20In%20column%20A%2C%20users%20input%20an%20'X'%20to%20make%20that%20row%20active%20which%20then%20gets%20copied%20for%20an%20email.%20If%20the%20row%20does%20not%20contain%20an%20'X'%2C%20the%20row%20is%20hidden%20and%20not%20copied%20for%20an%20email.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20I%20am%20trying%20to%20accomplish%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20a%20range%20of%20cells%20don't%20contain%20an%20'X'%2C%20THEN%20display%20%22general%20text%20message%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20none%20of%20the%20cells%20from%20A2%20through%20A10%20contain%20an%20'X'%2C%20then%20I%20want%20a%20message%20to%20be%20displayed%20in%20B1.%20However%2C%20if%20any%20of%20the%20cells%20from%20A2%20though%20A10%20contain%20an%20'X'%2C%20then%20B2%20should%20not%20display%20a%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20a%20way%20to%20accomplish%20this%2C%20is%20it%20possible%20to%20have%20the%20formula%20automatically%20adjust%20to%20a%20new%20range%20of%20cells%2C%20ie.%20if%20I%20were%20to%20add%20a%20row%20between%20A2%20and%20A10.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1394819%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394819%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20problemm%20where%20I%20need%20to%20filter%20out%20the%20state%20and%20district%20name%20from%20the%20address%20column%20and%20place%20the%20same%20in%20the%20adjoining%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EState%20llist%20can%20be%20arranged%20as%20a%20range.%20But%20what%20would%20be%20the%20formula%20to%20use%20and%20how%20will%20it%20be%20used%20to%20math%20the%20address%20column%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416785%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20was%20googling%20for%20a%20solution%20to%20the%20subject%20of%20above%20thread%20and%20found%20and%20used%20your%20solution%2C%3C%2FP%3E%3CP%3E%3CSPAN%3E%5BKey%20words%20in%20column%20F%2C%20returned%20text%20in%20column%20G%2C%20text%20to%20check%20in%20A1.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(PI()%2C1%2FCOUNTIF(A1%2C%22*%22%26amp%3B%24F%241%3A%24F%246%26amp%3B%22*%22)%2C%24G%241%3A%24G%246)%20%3C%2FSTRONG%3E%5Dwith%201%20problem%3B%20the%20keyword%20found%20doesn't%20display.%3C%2FP%3E%3CP%3Eusing%20Excel%202010%3C%2FP%3E%3CP%3Ewould%20appreciate%20any%20help%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1420878%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1420878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F679297%22%20target%3D%22_blank%22%3E%40Sanduke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20upload%20your%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell.

This formula seems to work for two variables but I can't add any more variables too it.

=IFERROR(IF(SEARCH("*Sales*",B3,1),"Sales"),IF(SEARCH("*Arch*",B3,1),"Architecture"))

 

The text I would be searching for would be:

Sales,

Arch,

Land,

ALL,

Contracts,

Construction

and possibly a couple more.

 

Is there a way to do this?

31 Replies
Highlighted

@Sergei Baklan 

 

I am looking for a formula to do the following. I believe it is an "IF", but not sure. I basically want a formula to return results based on the following:

 

If the cell with the number is <=50, then multiply by 2

If the cell with the number is >50, but <=100, then multiply by 1.75

If the cell with the number is >100, but <=200, then multiply by 1.50

 

I am trying to combine in one formula so that I can copy it all the way down the spreadsheet. Any help would be greatly appreciated.

 

 

Highlighted

@Detlef Lewin  Thank you so much - I spent so long looking for this - it works perfectly!

Highlighted

 

=A1*IF(A1>200,1,IF(A1>100,1.5,IF(A1>50,1.75,2)))
or
=A1*LOOKUP(A1,{0,50.000001,100.000001,200.000001},{2,1.75,1.5,1})

@bengal1022 

Highlighted

@Sergei Baklan this is exactly what I`m looking for but excel gives me an error - attached. 

Could you please help? 

1.JPG

Highlighted

@Detlef Lewin This works perfectly in most situations and is super simple, so I want to thank you for providing this! I haven't seen this solution offered anywhere else! The only problem I sometimes run into is that this isn't an exact match, so if I have a list of text that has some of the same terms or letter combinations, it sometimes returns the wrong thing. Is there a way to make this exact match only?

Highlighted

@krisi042 

Could you please attache the file instead of screenshort.

Highlighted

@Sergei Baklan I really like your solution with nested formatting, though I was wondering if there is a way to efficiently do a few hundred words?

 

There must be a better option than typing in all the words

@Freek_Olivier 

I gave nested IF only to illustrate that is workable. Formula which @Detlef Lewin suggested shall work perfectly, especially on big ranges.

Highlighted

@Reuben Helder I am working on a spreadsheet that contains macros. In column A, users input an 'X' to make that row active which then gets copied for an email. If the row does not contain an 'X', the row is hidden and not copied for an email. 

 

So what I am trying to accomplish: 

 

IF a range of cells don't contain an 'X', THEN display "general text message".

 

If none of the cells from A2 through A10 contain an 'X', then I want a message to be displayed in B1. However, if any of the cells from A2 though A10 contain an 'X', then B2 should not display a message.

 

If there is a way to accomplish this, is it possible to have the formula automatically adjust to a new range of cells, ie. if I were to add a row between A2 and A10.

 

 

Highlighted

I have a problemm where I need to filter out the state and district name from the address column and place the same in the adjoining column

 

State llist can be arranged as a range. But what would be the formula to use and how will it be used to math the address column 

Highlighted

@Detlef Lewin 

 

Hi was googling for a solution to the subject of above thread and found and used your solution,

[Key words in column F, returned text in column G, text to check in A1.

=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6) ]with 1 problem; the keyword found doesn't display.

using Excel 2010

would appreciate any help 

 

thanks!

  

Highlighted

@Sanduke 

Could you upload your file?