Forum Discussion

Reuben's avatar
Reuben
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's avatar
    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.

vijaykumar shetye's avatar
vijaykumar shetye
Copper Contributor
May 13, 2017

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, India

vijaykumar shetye's avatar
vijaykumar shetye
Copper Contributor
May 13, 2017

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

Resources