index match

Hi All,

I need a formula to identify keywords in a paragraph and assign the paragraph to a specific person. The goal is to determine a keyword in a given paragraph and then associate it with a designated individual


this formula works in excel.

=IF(ISNUMBER(SEARCH("scaffolding", A1)), "Summit",

 IF(ISNUMBER(SEARCH("electrical", A1)), "MCKAY",

 IF(ISNUMBER(SEARCH("paint", A1)), "Contract Coatings",

 IF(ISNUMBER(SEARCH("sprinkler", A1)), "Triangle",

 IF(ISNUMBER(SEARCH("strapping", A1)), "Forman",

 IF(ISNUMBER(SEARCH("sinage", A1)), "Hafele",

 IF(ISNUMBER(SEARCH("tiling", A1)), "Tile people", "")))))))

i try this formula but not working

=IFERROR(

 IF(

  OR(

   SEARCH("Electrical", [Task Name]@row),

   SEARCH("PVC", [Task Name]@row),

   SEARCH("Data", [Task Name]@row)

  ),

  VLOOKUP(

   INDEX(

    { "Electrical", "PVC", "Data" },

    MATCH(TRUE, ISNUMBER(SEARCH(

     { "Electrical", "PVC", "Data" },

     [Task Name]@row

    )))

   ),

   {test1 Range 2},

   2,

   FALSE

  ),

  "No Match"

 ),

 "No Match"

)



Thanks Alister

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Alister

    Try this instead:

    =IF(FIND("scaffolding", [Task Status]@row) > 0, "Summit", IF(FIND("electrical", [Task Status]@row) > 0, "MCKAY", IF(FIND("paint", [Task Status]@row) > 0, "Contract Coatings", IF(FIND("sprinkler", [Task Status]@row) > 0, "Triangle", IF(FIND("strapping", [Task Status]@row) > 0, "Forman", IF(FIND("sinage", [Task Status]@row) > 0, "Hafele", IF(FIND("tiling", [Task Status]@row) > 0, "Tile people", "")))))))

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Alister

    Try this instead:

    =IF(FIND("scaffolding", [Task Status]@row) > 0, "Summit", IF(FIND("electrical", [Task Status]@row) > 0, "MCKAY", IF(FIND("paint", [Task Status]@row) > 0, "Contract Coatings", IF(FIND("sprinkler", [Task Status]@row) > 0, "Triangle", IF(FIND("strapping", [Task Status]@row) > 0, "Forman", IF(FIND("sinage", [Task Status]@row) > 0, "Hafele", IF(FIND("tiling", [Task Status]@row) > 0, "Tile people", "")))))))

    Does this work for you?

    Kelly

  • Alister
    Alister ✭✭

    Thanks, Kelly Yes Yes works :)😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!