The scenario

Here is what I started doing and then thought vba must be better

=IF(ISNUMBER(SEARCH("Red",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("Ron",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("cat",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("cat1",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("air",Data1!B2)),"P2",IF(ISNUMBER(SEARCH("Cog",Data1!B2)),"P2"))))))+IF(ISNUMBER(SEARCH("Sul",Data1!B2)),"P2")

Thank you for any help

Lee