Forum Discussion
HOW TO: "If cell contains specific text then return specific text"
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.
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'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.
=IFERROR(INDEX($G$1:$G$6,AGGREGATE(15,3,SEARCH($G$1:$G$6,A1)*ROW($G$1:$G$6),1),1), "No Match") * AGGREGATE can ignore errorSorry, I didn't catch what's that about. If about recent post, I'd prefer formula which DMColleran used before
=LOOKUP(PI(),1/COUNTIF(TargetCell,"*"&KeywordRange&"*"),CannabisRange)it works better than SEARCH() within AGGREGATE().
As for the concrete formula, it won't work for the sample from previous post, shall be adjusted.
SergeiBaklan I really like your solution with nested formatting, though I was wondering if there is a way to efficiently do a few hundred words?
There must be a better option than typing in all the words
I gave nested IF only to illustrate that is workable. Formula which Detlef_Lewin suggested shall work perfectly, especially on big ranges.
I am looking for a formula to do the following. I believe it is an "IF", but not sure. I basically want a formula to return results based on the following:
If the cell with the number is <=50, then multiply by 2
If the cell with the number is >50, but <=100, then multiply by 1.75
If the cell with the number is >100, but <=200, then multiply by 1.50
I am trying to combine in one formula so that I can copy it all the way down the spreadsheet. Any help would be greatly appreciated.
=A1*IF(A1>200,1,IF(A1>100,1.5,IF(A1>50,1.75,2))) or =A1*LOOKUP(A1,{0,50.000001,100.000001,200.000001},{2,1.75,1.5,1})
