index match

Alister

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

Kelly Moore

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

Kelly Moore

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

Thanks, Kelly Yes Yes works :)😀