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%3CLINGO-SUB%20id%3D%22lingo-sub-1853618%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-1853618%22%20slang%3D%22en-US%22%3EIs%20there%20an%20equivalent%20way%20to%20use%20this%20Excel%20logic%20in%20Power%20Query%20were%20the%20source%20list%20of%20text%20string%20is%20a%20table%20or%20another%20query%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1854446%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-1854446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857434%22%20target%3D%22_blank%22%3E%40DMColleran%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20what%20exactly%20you'd%20like%20to%20do%2C%20perhaps%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3D%20Table.SelectRows(%0A%20%20%23%22Table%20To%20Filter%22%20%2C%0A%20%20each%20List.Contains(%0A%20%20%20%20%20%20%20%20%20%23%22Table%20With%20Filter%22%5BColumnWithValues%5D%2C%0A%20%20%20%20%20%20%20%20%20%5BColumnOnWhichWeFilterSourceTable%5D%0A%20%20%20%20%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1854646%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-1854646%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%3Bwe%20have%20a%20data%20cleanliness%20issue%2C%20whereby%20a%20freeform%20field%20contains%20key%20information%20about%20each%20entry.%20Inside%20the%20freeform%20field%20are%20keywords%20which%20we%20want%20to%20pull%20out%20into%20a%20new%20column.%20The%20%3CEM%3E%3DLOOKUP(PI()%2C1%2FCOUNIF(TargetCell%2C%22*%22%26amp%3BKeywordRange%26amp%3B%22*%22)%2CKeywordRange)%20%3C%2FEM%3EFormula%20works%20well%20in%20an%20Excel%20sheet%2C%20however%20our%20dataset%20is%20much%20larger%20and%20we%20will%20need%20to%20handle%20it%20in%20PQ%20or%20some%20other%20tool.%20Essentially%20we%20want%20perform%20the%20same%20operation%20as%20the%20above%20function%20but%20within%20PQ.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sample%20of%20the%20dataset%2C%20with%20tables%20showing%20the%20raw%20data%20and%20target%20keywords%20(INPUTS)%20as%20well%20as%20a%20sample%20output%20of%20cleaned%20data%20(OUTPUT).%20I%20achieved%20the%20example%20output%20by%20using%20a%20fuzzy%20match%20in%20PQ%2C%20but%20that%20is%20not%20necessarily%20a%20perfect%20resolution.%20Perhaps%20it%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1854882%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-1854882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857434%22%20target%3D%22_blank%22%3E%40DMColleran%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'%20not%20sure%20how%20the%20table%20with%20keywords%20works.%20For%20example%2C%20keyword%20%3CSTRONG%3Ea%3C%2FSTRONG%3E%20is%20met%20in%20every%20word%20for%20several%20times.%20Thus%20simply%20try%20to%20reproduce%20what%20LOOKUP()%20do.%20Second%20part%20of%20your%20query%2C%20instead%20of%20fuzzy%20merging%2C%20is%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%20%20%20%20%23%22Lowercased%20Text%22%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20%23%22Removed%20Columns%22%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Description%22%2C%20Text.Lower%2C%20type%20text%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20KeywordsTable%20%3D%20CannaFormKeywords%2C%0A%20%20%20%20%23%22Add%20Cannabis%20Form%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Lowercased%20Text%22%2C%0A%20%20%20%20%20%20%20%20%22Cannabis%20Form%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20let%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20txt%20%3D%20%5BDescription%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lst%20%3D%20KeywordsTable%5BKeyword%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20out%20%3D%20KeywordsTable%5BCannabis%20Form%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20transformed%20%3D%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lst%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20Text.Contains(txt%2C%20_)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20out%7BList.PositionOf(lst%2C_)%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20cleaned%20%3D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.Last(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.RemoveNulls(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20transformed%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20in%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cleaned)%2C%0A%20%20%20%20%23%22Remove%20source%20texts%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Add%20Cannabis%20Form%22%2C%0A%20%20%20%20%20%20%20%20%7B%22ID%22%2C%20%22Date%22%2C%20%22Cannabis%20Form%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Remove%20source%20texts%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(see%20duplicated%20query).%26nbsp%3B%3C%2FP%3E%0A%3CP%3EResult%20looks%20the%20same%20as%20with%20fuzzy%20match%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20599px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231525i96F17AD38D4CE408%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%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?

35 Replies
Highlighted

Ah, a classic one.

 

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)

 

Highlighted

Dear Reuben Helder,

You may also use the below formula for the task. I have retained the same cell references that have been used by Detlef Lewin in the solution that he has provided.
=INDEX($F$1:$F$6,SUM(COUNTIF($A1,"*"&$F$1:$F$6&"*")*ROW(F1:F6)))

The formula given by Detlef Lewin can be amended, so that Pi() and column G will not be required.
=LOOKUP(1,1/COUNTIF($A1,"*"&$F$1:$F$6&"*"),$F$1:$F$6)

Vijaykumar Shetye
Spreadsheet Excellence,
Panaji, Goa, India

Highlighted

Dear Reuben Helder,

Kindly note that the below formula which I had given in the provious post, is an array formula.
=INDEX($F$1:$F$6,SUM(COUNTIF($A1,"*"&$F$1:$F$6&"*")*ROW($F$1:$F$6)))

Array formulas are entered using Control+Shift+Enter instead of Enter.

Vijaykumar Shetye
Spreadsheet Excellence,
Panaji, Goa, India

Highlighted

Vijaykumar Shetye, my formula does not need amending.

And your change from PI() to 1 will possibly give wrong results.

 

 

 

Highlighted

Dear Detlef Lewin,

Thanks for the reply and the correction. What is the significance of using Pi() in the formula?

 

Viaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

Highlighted

PI() itself is just a little gag. The lookup value has to be a number greater than 1. So 1.1 would be enough or 2 if you prefer whole numbers.

 

Highlighted

Hi Reuben Helder,

 

Good day,

 

Please refer below formula to add more variable.

If statment is false then I have considered criteria is "NO MATCH"

IF(B3="MECH","MECHANICAL",IF(B3="ARCH","ARCHITECTURE",IF(B3="SALES","SALES",IF(B3="ELE","ELECTRICAL","NO MATCH"))))

 

Is this helful for you? please revert bach.

 

 

Thanks & regards,

Manoj P.

 

 

 

Highlighted

Hi Manoj,

 

It's worth to re-read task requirements and previous posts, your formula is about something different.

Highlighted

Dear Manoj Patgar,

(1)  The problem has been posted on the forum by Reuben Helder because the formula using nested If functions is not adequate, due to its limitations.

The number of items in the text list is large (Sales, Arch, Land, ALL, Contracts, Construction and possibly a couple more), which nested IFs cannot handle. So the same formula with minor changes cannot work. The formula to be used has to be disigned to handle a long list of data.

 

(2)  The data which is being searched is not MECH or ARCH. It will be like Searching MECH or ARCH within a data list that contain entries like 'ABC MECHANICAL ENGINEERS' or 'XYX ARCHITECTS'. Hence the wild card character (*) has been used.

 

Is it clear now?

 

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa India

 

 

 

Highlighted
Best Response confirmed by Reuben Helder (Contributor)
Solution

I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.

 

But what to use depends on goals, in some cases quick hardcording works quite fine.

 

If use formatting nested if becomes much more clear and editable. Like this

=IF(ISNUMBER(SEARCH("Sales",        B3,1)),"Sales",
IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",
IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",
IF(ISNUMBER(SEARCH("All", B3,1)),"All",
IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",
IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
"No Match"
))))))

 

If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.

 

 =IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1,
  IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2,
  IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3,
  IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4,
  IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5,
  IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6,
  IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7,
  IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8,
  IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9,
    "No Match"
 )))))))))
 

I don't vote for nested IF,  i would like to say where is no limitations here. What to use that's concrete person choice.

Highlighted

Dear Mr. Sergi,

 

Yes. You are absolutely right.

 

<it is worth to re-read the question>

 

I am a beginner in excel and I am always welcome one who gives valuable suggestion.

 

With best regards,

Manoj.

Highlighted

Dear Mr. Vijaykumar,

 

Thank you and appreciate your support.

I am beginner and I am always welcome one who want to give valuable suggestions.

 

Thanks with best regards,

Manoj.

Highlighted

Hi I'm looking to create a formula to calculate how much I spend on groceries a year. For an example:

 

=IF(B1="nofrills","Loblaws","sobeys"

then display C1 (the $$)

otherwise false

Highlighted

@scmallory , that's a separate question, better to start new conversation with it from here.

Formula could be

=IF(SUM(--(B1={"nofrills","Loblaws","sobeys"})),C1)

 

Highlighted

@Sergei BaklanI'm having troubles posting in a new thread.

 

I tested out the formula and I was hoping cells in column D it would spit out the value in column C for anywhere it says "Food Basic" in column B. So the attached Test document.

Thank you,

 

Highlighted

@scmallory , to start new conversation go to the General Discussion as in my link and click

image.png 

 

Formula doesn't work since in column B you have "food basics ", and you compare with "food basics". Due to space texts are different. Trim the cell like

=IF(SUM(--(TRIM(B1)={"nofrills","food basics","Loblaws","sobeys"})),C1)
Highlighted
Highlighted

@scmallory , good, thank you for the confirmation

Highlighted
Not OP, but this was exactly what I was looking for, thank you!