SOLVED
Home

Searching For Text

%3CLINGO-SUB%20id%3D%22lingo-sub-615009%22%20slang%3D%22en-US%22%3ESearching%20For%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615009%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20create%20a%20formula%20that%20searches%20a%20cell%20full%20of%20text%20and%20if%20it%20contains%20words%20from%20a%20certain%20list%2C%20the%20cell%20will%20display%20%220%22%2C%20if%20it%20contains%20words%20from%20a%20different%20list%2C%20then%20it%20will%20display%20a%20%221%22%20all%20the%20way%20up%20to%204.%20All%20of%20the%20equations%20I%20have%20tried%20are%20IF%20functions%20that%20return%20only%20a%20TRUE%2FFALSE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20text%20I%20want%20to%20search%20is%20the%20descriptor%20attached%20to%20the%20students%20on%20the%20right.%20On%20the%20left%20is%20a%20table%20of%20the%20words%20that%20are%20command%20terms%20for%20each%20level.%20I%20hope%20this%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-615009%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615414%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20For%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344914%22%20target%3D%22_blank%22%3E%40LWMorton%3C%2FA%3E%26nbsp%3B%2C%20perhaps%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUMPRODUCT(--ISNUMBER(SEARCH(%24AF%242%3A%24AF%248%2CB2)))%2C%0A%20%20%20%20%20%204%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT(--ISNUMBER(SEARCH(%24AE%242%3A%24AE%248%2CB2)))%2C%0A%20%20%20%20%20%203%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT(--ISNUMBER(SEARCH(%24AD%242%3A%24AD%248%2CB2)))%2C%0A%20%20%20%20%20%202%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT(--ISNUMBER(SEARCH(%24AC%242%3A%24AC%248%2CB2)))%2C%0A%20%20%20%20%20%201))))%3C%2FPRE%3E%0A%3CP%3Ein%20C2%20and%20copy%20it%20to%20another%20cells%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-619182%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20For%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-619182%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%3Bthat%20has%20worked%20perfectly%2C%20you%20are%20my%20saviour!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-619248%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20For%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-619248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344914%22%20target%3D%22_blank%22%3E%40LWMorton%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-622642%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20For%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622642%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%3Bfor%20some%20reason%20only%20half%20the%20equation%20works%2C%20the%20terms%20for%20level%200%20and%201%20are%20coming%20up%20as%202s.%20Any%20ideas%20to%20fix%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624255%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20For%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344914%22%20target%3D%22_blank%22%3E%40LWMorton%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20forgot%20about%200%20column.%20Plus%20we%20have%20to%20exclude%20blank%20cells%2C%20finally%20as%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUMPRODUCT((LEN(%24AF%242%3A%24AF%248)%26gt%3B0)*ISNUMBER(SEARCH(%24AF%242%3A%24AF%248%2CB2)))%2C%0A%20%20%20%20%20%204%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT((LEN(%24AE%242%3A%24AE%248)%26gt%3B0)*ISNUMBER(SEARCH(%24AE%242%3A%24AE%248%2CB2)))%2C%0A%20%20%20%20%20%203%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT((LEN(%24AD%242%3A%24AD%248)%26gt%3B0)*ISNUMBER(SEARCH(%24AD%242%3A%24AD%248%2CB2)))%2C%0A%20%20%20%20%20%202%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT((LEN(%24AC%242%3A%24AC%248)%26gt%3B0)*ISNUMBER(SEARCH(%24AC%242%3A%24AC%248%2CB2)))%2C%0A%20%20%20%20%20%201%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT((LEN(%24AB%242%3A%24AB%248)%26gt%3B0)*ISNUMBER(SEARCH(%24AB%242%3A%24AB%248%2CB2)))%2C%0A%20%20%20%20%20%200%0A)))))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
LWMorton
New Contributor

I am looking to create a formula that searches a cell full of text and if it contains words from a certain list, the cell will display "0", if it contains words from a different list, then it will display a "1" all the way up to 4. All of the equations I have tried are IF functions that return only a TRUE/FALSE

 

The text I want to search is the descriptor attached to the students on the right. On the left is a table of the words that are command terms for each level. I hope this makes sense.

5 Replies
Solution

@LWMorton , perhaps

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1))))

in C2 and copy it to another cells

@Sergei Baklan that has worked perfectly, you are my saviour!

@LWMorton , glad to help

@Sergei Baklan for some reason only half the equation works, the terms for level 0 and 1 are coming up as 2s. Any ideas to fix?

@LWMorton ,

 

Sorry, I forgot about 0 column. Plus we have to exclude blank cells, finally as

=IF(SUMPRODUCT((LEN($AF$2:$AF$8)>0)*ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT((LEN($AE$2:$AE$8)>0)*ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT((LEN($AD$2:$AD$8)>0)*ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT((LEN($AC$2:$AC$8)>0)*ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1,
      IF(SUMPRODUCT((LEN($AB$2:$AB$8)>0)*ISNUMBER(SEARCH($AB$2:$AB$8,B2))),
      0
)))))