SOLVED

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

Copper 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

 

46 Replies

@SBays123 

Do you really need the table on the right to include all the PSI's from the table on the left?

Or would it be OK to list just the Flow for the integer PSI values 70, 69, ..., 7?

@Hans Vogelaar 

 

As long as the table on the right has PSI values from the max (in this case 70.41) down to the min (6.81) in increments of 1 PSI that will work fine. BUT the tricky part is ensuring that the interpolation formula is being updated as you go from one range to another.

 

For example, from 70.41 down to 66.66 you will use the first two corresponding Y-values (0 & 759.4), but once you get to 66.66 and until you get to 63.48 you will need the corresponding Y-values of 759.4 & 1015.24.

 

I dont necessarily need all the PSI values on the left to be on the right, as long as the interpolation function corresponds is updated with the next set of table data as you get into the new ranges thats all that matters.

 

Hopefully that makes sense. 

Long story short yes that is fine as long as the interpolation points are updated as you get into the different ranges.

@SBays123 

The attached sample workbook uses dynamic array formulas available in Excel in Microsoft 365 and Office 2021, and in Excel Online.

@Hans Vogelaar 

 

I believe this is working correctly for what I need, are you able to briefly explain the formula? I get the XLOOKUP function searches for a value in an array and it is outputting the nearest value, but i am struggling where you got the function from that adds, subtracts, divides, and multiplies all the XLOOKUPs. 

@SBays123 Given a PSI (70, 69, ...), the formula looks for the next lower and next higher PSI in the table on the left. It uses these to look up the Flow values corresponding to those PSIs.

Finally, it finds the Flow for the given PSI by linear interpolation.

I designed the formula by creating separate formulas for each of the parts first, then combining them into one monster formula,

@Hans Vogelaar 

 

Makes sense, and it works! thank you much!

@Hans Vogelaar
You seem to be very knowledgeable about this. I have a similar question with more basic excel skills. I was trying to use a Vlookup but the data value I'm searching is actually a cell with a string of text and numbers (ie. AB FEE INV# 123456). If it finds a key word (there is at least 12-15 words to search) then I want it to output the corresponding flag (ie EXCLUDE, or INDX --also about 8-10 different words). Vlookup seems to work for this except the data value is not a single word or number and the key word is embedded within the text string.
Any suggestions? Thank you

@A_Arf23 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Please indicate what you want in the workbook.

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?

 

doc. w-f.png

@nowzar 

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.

dear Hans 

Hi

Thank you so much for your rapid response and quite helpful formulas.  

@Hans Vogelaar 

 

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

 

_L_C__0-1678716343481.png

 

this is the formula I typed into the screenshot above:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH({"Hunger","Food","Feeding","Animals","Pets"},A2)),{"Hunger","Animals"},""))

 

@_L_C_ 

It should be

 

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Animals","Food","Pets"},A2)),{"Animals","Hunger","Animals"},""))

@Hans Vogelaar 

 

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!

 

_L_C__0-1678718326353.png

 

@_L_C_ 

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?

That's correct. Searching for multiple keywords and returning one word, ultimately allowing me to categorize all my data under 5-7 buckets

@_L_C_ 

That requires a different formula. See the attached demo workbook.

Thanks so much Hans. You are a lifesaver!

@Santhosh_V 

 

I am trying to create a formula to search for the words MR and Meeting room; however, I keep receiving an error. Any suggestions would be appreciated!

 

=IF(ISNUMBER(SEARCH({"MR","Meeting Room"},S2))=TRUE,{"MR","Meeting Room"},FALSE)