Forum Discussion

Iron Contributor

May 12, 2017

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...

Formulas & Functions

Requests

SergeiBaklan

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.

SergeiBaklan

Diamond Contributor

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.

Willy Lau

Iron Contributor

Dec 11, 2020

SergeiBaklan

=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

SergeiBaklan
Diamond Contributor
Dec 11, 2020
Willy Lau

Sorry, 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.