Forum Discussion
Solved
HOW TO: "If cell contains specific text then return specific text"
I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell.
This formula seems to work for two variables but I can't add any more variables too it.
=IFERROR(IF(SEARCH("*Sales*",B3,1),"Sales"),IF(SEARCH("*Arch*",B3,1),"Architecture"))
The text I would be searching for would be:
Sales,
Arch,
Land,
ALL,
Contracts,
Construction
and possibly a couple more.
Is there a way to do this?
I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.
But what to use depends on goals, in some cases quick hardcording works quite fine.
If use formatting nested if becomes much more clear and editable. Like this
=IF(ISNUMBER(SEARCH("Sales", B3,1)),"Sales",
IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",
IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",
IF(ISNUMBER(SEARCH("All", B3,1)),"All",
IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",
IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
"No Match"
))))))
If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.
=IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1, IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2, IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3, IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4, IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5, IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6, IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7, IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8, IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9, "No Match" )))))))))I don't vote for nested IF, i would like to say where is no limitations here. What to use that's concrete person choice.
Reuben I am working on a spreadsheet that contains macros. In column A, users input an 'X' to make that row active which then gets copied for an email. If the row does not contain an 'X', the row is hidden and not copied for an email.
So what I am trying to accomplish:
IF a range of cells don't contain an 'X', THEN display "general text message".
If none of the cells from A2 through A10 contain an 'X', then I want a message to be displayed in B1. However, if any of the cells from A2 though A10 contain an 'X', then B2 should not display a message.
If there is a way to accomplish this, is it possible to have the formula automatically adjust to a new range of cells, ie. if I were to add a row between A2 and A10.
Hi I'm looking to create a formula to calculate how much I spend on groceries a year. For an example:
=IF(B1="nofrills","Loblaws","sobeys"
then display C1 (the $$)
otherwise false
scmallory , that's a separate question, better to start new conversation with it from here.
Formula could be
=IF(SUM(--(B1={"nofrills","Loblaws","sobeys"})),C1)SergeiBaklanI'm having troubles posting in a new thread.
I tested out the formula and I was hoping cells in column D it would spit out the value in column C for anywhere it says "Food Basic" in column B. So the attached Test document.
Thank you,
Hi Reuben Helder,
Good day,
Please refer below formula to add more variable.
If statment is false then I have considered criteria is "NO MATCH"
IF(B3="MECH","MECHANICAL",IF(B3="ARCH","ARCHITECTURE",IF(B3="SALES","SALES",IF(B3="ELE","ELECTRICAL","NO MATCH"))))
Is this helful for you? please revert bach.
Thanks & regards,
Manoj P.
Dear Manoj Patgar,
(1) The problem has been posted on the forum by Reuben Helder because the formula using nested If functions is not adequate, due to its limitations.
The number of items in the text list is large (Sales, Arch, Land, ALL, Contracts, Construction and possibly a couple more), which nested IFs cannot handle. So the same formula with minor changes cannot work. The formula to be used has to be disigned to handle a long list of data.
(2) The data which is being searched is not MECH or ARCH. It will be like Searching MECH or ARCH within a data list that contain entries like 'ABC MECHANICAL ENGINEERS' or 'XYX ARCHITECTS'. Hence the wild card character (*) has been used.
Is it clear now?
Vijaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa India
I agree and your value add help me no end. Thanks and keep up the great dialogue.
Hi Manoj,
It's worth to re-read task requirements and previous posts, your formula is about something different.
Dear Mr. Sergi,
Yes. You are absolutely right.
<it is worth to re-read the question>
I am a beginner in excel and I am always welcome one who gives valuable suggestion.
With best regards,
Manoj.
Dear Reuben Helder,
You may also use the below formula for the task. I have retained the same cell references that have been used by Detlef Lewin in the solution that he has provided.
=INDEX($F$1:$F$6,SUM(COUNTIF($A1,"*"&$F$1:$F$6&"*")*ROW(F1:F6)))
The formula given by Detlef Lewin can be amended, so that Pi() and column G will not be required.
=LOOKUP(1,1/COUNTIF($A1,"*"&$F$1:$F$6&"*"),$F$1:$F$6)
Vijaykumar Shetye
Spreadsheet Excellence,
Panaji, Goa, IndiaVijaykumar Shetye, my formula does not need amending.
And your change from PI() to 1 will possibly give wrong results.
Dear Detlef Lewin,
Thanks for the reply and the correction. What is the significance of using Pi() in the formula?
Viaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa, India
Dear Reuben Helder,
Kindly note that the below formula which I had given in the provious post, is an array formula.
=INDEX($F$1:$F$6,SUM(COUNTIF($A1,"*"&$F$1:$F$6&"*")*ROW($F$1:$F$6)))
Array formulas are entered using Control+Shift+Enter instead of Enter.
Vijaykumar Shetye
Spreadsheet Excellence,
Panaji, Goa, India
Ah, a classic one.
Key words in column F, returned text in column G, text to check in A1.
=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6)
- Is there an equivalent way to use this Excel logic in Power Query were the source list of text string is a table or another query?
Not sure what exactly you'd like to do, perhaps something like
= Table.SelectRows( #"Table To Filter" , each List.Contains( #"Table With Filter"[ColumnWithValues], [ColumnOnWhichWeFilterSourceTable] ) )
Hi was googling for a solution to the subject of above thread and found and used your solution,
[Key words in column F, returned text in column G, text to check in A1.
=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6) ]with 1 problem; the keyword found doesn't display.
using Excel 2010
would appreciate any help
thanks!
I have a problemm where I need to filter out the state and district name from the address column and place the same in the adjoining column
State llist can be arranged as a range. But what would be the formula to use and how will it be used to math the address column
