SOLVED

Need to search multiple words in a cell and get the output based on the word found.

%3CLINGO-SUB%20id%3D%22lingo-sub-2490856%22%20slang%3D%22en-US%22%3ENeed%20to%20search%20multiple%20words%20in%20a%20cell%20and%20get%20the%20output%20based%20on%20the%20word%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2490856%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20a%20data%20in%20column%20A%20like%20below%2C%20then%20I%20need%20search%20for%20multiple%20words%20like%20%22Generic%20Mailbox%22%2C%20%22Distribution%22%2C%20%22Non-standard%22%2C%20%22NSSR%22%20in%20the%20each%20cell%20and%20then%3C%2FP%3E%3CP%3Eif%20%22Generic%20mailbox%22%20is%20founds%20in%20the%20cell%2C%20output%20should%20be%20%22shared%20mailbox%22%20or%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%22Distribution%22%26nbsp%3Bis%20founds%20in%20the%20cell%2C%20output%20should%20be%20%22DL%22%20or%26nbsp%3B%3C%2FP%3E%3CP%3EIf%26nbsp%3B%22Non-standard%22%26nbsp%3Bis%20founds%20in%20the%20cell%2C%20output%20should%20be%20%22Corporate%20request%22%20or%3C%2FP%3E%3CP%3EIf%26nbsp%3B%22NSSR%22%26nbsp%3Bis%20founds%20in%20the%20cell%2C%20output%20should%20be%20%22Non-Standard%20Service%20request%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20for%20to%20get%20help%20on%20this!!%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Santhosh_V_0-1624820039384.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291830i78821A3FCA1EB264%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Santhosh_V_0-1624820039384.png%22%20alt%3D%22Santhosh_V_0-1624820039384.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2490856%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2490889%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20search%20multiple%20words%20in%20a%20cell%20and%20get%20the%20output%20based%20on%20the%20word%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2490889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1005252%22%20target%3D%22_blank%22%3E%40Santhosh_V%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%202019%20or%20Excel%20in%20Microsoft%20365%2C%20enter%20the%20following%20formula%20in%20B1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(ISNUMBER(SEARCH(%7B%22Generic%20Mailbox%22%2C%22Distribution%22%2C%22Non-standard%22%2C%22NSSR%22%7D%2CA1))%2C%7B%22Shared%20Mailbox%22%2C%22DL%22%2C%22Corporate%20Request%22%2C%22Non-Standard%20Service%20Request%22%7D%2C%22%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20allows%20for%20more%20than%20one%20of%20the%20search%20terms%20in%20a%20cell.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOtherwise%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(%7B%22Shared%20Mailbox%22%2C%22DL%22%2C%22Corporate%20Request%22%2C%22Non-Standard%20Service%20Request%22%7D%2CMATCH(TRUE%2CISNUMBER(SEARCH(%7B%22Generic%20Mailbox%22%2C%22Distribution%22%2C%22Non-standard%22%2C%22NSSR%22%7D%2CA1))%2C0))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20from%20B1.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I am having a data in column A like below, then I need search for multiple words like "Generic Mailbox", "Distribution", "Non-standard", "NSSR" in the each cell and then

if "Generic mailbox" is founds in the cell, output should be "shared mailbox" or 

If "Distribution" is founds in the cell, output should be "DL" or 

If "Non-standard" is founds in the cell, output should be "Corporate request" or

If "NSSR" is founds in the cell, output should be "Non-Standard Service request"

 

Looking for to get help on this!!

Thank you. 

 

Santhosh_V_0-1624820039384.png

 

2 Replies
best response confirmed by Santhosh_V (Occasional Contributor)
Solution

@Santhosh_V 

If you have Excel 2019 or Excel in Microsoft 365, enter the following formula in B1:

 

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),{"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},""))

 

This allows for more than one of the search terms in a cell.

 

Otherwise:

 

=IFERROR(INDEX({"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},MATCH(TRUE,ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),0)),"")

 

Fill down from B1.