Forum Discussion
Need to search multiple words in a cell and get the output based on the word found.
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.
Dear Hans
as you see in the attached photo, I have too much data under the Document title (the Ccolumn), that needs to have a related weight factor (in cells under column H) according to the text and numbers in row 1&2.
I need the formula to search any texts of row1 in the cells of the C column and put the related number of row 2 in the cell in front of each sentence (i,e "conceptual" refers to "0.06%")
would you please help me in this regard?
In H3:
=IFERROR(INDEX($J$2:$P$2,MATCH(TRUE,ISNUMBER(SEARCH($J$1:$P$1,C3)),0)),"")
or
=IFERROR(LOOKUP(2,1/SEARCH($J$1:$P$1,C3),$J$2:$P$2),"")
Format H3 as a percentage with 2 decimal places, then fill down.
select * from Sheet2;
select * from Sheet3;
select * from Sheet2 left join Sheet3 where instr(Sheet2.Phrase,KeyWord)>0;
Phrase
Searching for Food Kidney Charity Pet Shop Missing a Meal Heart Clinic I have two Dogs Keyword Category
Animal Animals Pet Animals Cat Animals Dog Animals Cancer Health Kidney Health Heart Health Diabetes Health Food Hunger Meal Hunger Famine Hunger Phrase Keyword Category
Searching for Food Food Hunger Kidney Charity Kidney Health Pet Shop Pet Animals Missing a Meal Meal Hunger Heart Clinic Heart Health I have two Dogs Dog Animals - Thanks so much Hans. You are a lifesaver!
That requires a different formula. See the attached demo workbook.
- That's correct. Searching for multiple keywords and returning one word, ultimately allowing me to categorize all my data under 5-7 buckets
This discussion was about returning multiple words if the cell being searched contained more than one of the keywords. I get the impression that you want to return only one word. Is that correct?
Thank you for such a quick reply!
Your fix did resolve my issue, but I'm wondering if there is a way to simplify or shorten this formula?
The issue I'm having is that I need the formula to search for multiple keywords and return the same output. The intent is to place different terms under one bucket category so to speak.
For ex: column A2 could be 'downtown pets' and the output should be 'animals'. Column A3 could say 'searching for animals' and the output should still be 'animals'
Similarly, I want to put anything that refers to cancer, kidney, heart in column A under 'health' as the output.
Your formula works for this purpose. It's just extremely long as I have a lot of different terms I need to search. Not sure if there is an enhanced way to perform this function? Appreciate any assistance you can provide!
It should be
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Animals","Food","Pets"},A2)),{"Animals","Hunger","Animals"},""))
I tried to copy your formula responding to the original question in this thread. However, I must be doing something wrong because my formula is automatically returning the last text I had listed instead of searching for the appropriate output.
If text in column A contains Food - output should be Hunger
If text in column A contains Animals - output should be Animals
If text in column A contains Pets - output should be Animals
this is the formula I typed into the screenshot above:
=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH({"Hunger","Food","Feeding","Animals","Pets"},A2)),{"Hunger","Animals"},""))
dear Hans
Hi
Thank you so much for your rapid response and quite helpful formulas.
