Forum Discussion
Reuben
May 12, 2017Iron Contributor
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...
- May 14, 2017
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.
manoj patgar
May 14, 2017Copper Contributor
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.
- vijaykumar shetyeMay 14, 2017Brass Contributor
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
- onderayMay 06, 2021Copper Contributor
I agree and your value add help me no end. Thanks and keep up the great dialogue.
- manoj patgarMay 16, 2017Copper Contributor
Dear Mr. Vijaykumar,
Thank you and appreciate your support.
I am beginner and I am always welcome one who want to give valuable suggestions.
Thanks with best regards,
Manoj.
- SergeiBaklanMay 14, 2017MVP
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.
- Willy LauDec 11, 2020Steel Contributor
=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 error
- SergeiBaklanMay 14, 2017MVP
Hi Manoj,
It's worth to re-read task requirements and previous posts, your formula is about something different.
- manoj patgarMay 16, 2017Copper Contributor
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.