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.

SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 12, 2019

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)

 

scmallory's avatar
scmallory
Copper Contributor
Apr 12, 2019

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,

 

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 12, 2019

    scmallory , to start new conversation go to the General Discussion as in my link and click

     

     

    Formula doesn't work since in column B you have "food basics ", and you compare with "food basics". Due to space texts are different. Trim the cell like

    =IF(SUM(--(TRIM(B1)={"nofrills","food basics","Loblaws","sobeys"})),C1)
    • krisi042's avatar
      krisi042
      Copper Contributor
      Oct 28, 2019

      SergeiBaklan this is exactly what I`m looking for but excel gives me an error - attached. 

      Could you please help? 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Dec 30, 2019

        krisi042 

        Could you please attache the file instead of screenshort.

Resources