Jun 27 2021 12:02 PM
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.
Jan 31 2023 01:49 PM
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?
Jan 31 2023 02:57 PM
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.
Jan 31 2023 02:59 PM
Jan 31 2023 03:17 PM
The attached sample workbook uses dynamic array formulas available in Excel in Microsoft 365 and Office 2021, and in Excel Online.
Jan 31 2023 05:47 PM
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.
Feb 01 2023 12:48 AM
@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,
Feb 01 2023 07:22 AM
Feb 01 2023 01:52 PM
Feb 01 2023 03:13 PM
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.
Feb 19 2023 03:38 AM
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?
Feb 19 2023 04:13 AM
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.
Feb 19 2023 08:54 PM
dear Hans
Hi
Thank you so much for your rapid response and quite helpful formulas.
Mar 13 2023 07:07 AM
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"},""))
Mar 13 2023 07:17 AM - edited Mar 13 2023 07:42 AM
It should be
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Animals","Food","Pets"},A2)),{"Animals","Hunger","Animals"},""))
Mar 13 2023 07:39 AM - edited Mar 13 2023 07:40 AM
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!
Mar 13 2023 07:43 AM
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?
Mar 13 2023 07:46 AM
Mar 13 2023 07:56 AM
That requires a different formula. See the attached demo workbook.
Mar 13 2023 08:02 AM
May 23 2023 12:57 PM
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)