Multi-Conditional IF + AND + ISNUMBER + FIND Statement

Hi Folks. This one is a bit beyond my skill level. Any advice would be greatly appreciated. I require a single formula to do the following:

If value in worksheet 'NI&O Clarity Construction Dump'!$J:$J is equal to the value in cell $A2 of the current worksheet
And that corresponding text value in column J contains the text "ISP" and "Only", then populate value of "ISP Only"
If that corresponding text value in column J contains the text "OSP" and "Only", then populate "OSP Only" in formula cell
If that corresponding text value in column J contains the text "ISP" and "OSP", then populate "ISP & OSP" in formula cell
and if the corresponding text value in column J is blank, then populate whatever value is in column I:I

The text string does not always contain ISP Only in that order, or OSP Only in that order etc., so an AND statement would have to be added to find that the two text strings appear anywhere in the cell being referenced.

Am I up a creek? O__O

Thanks for looking. Aimee

I updated the code you provided to be more along the lines of what I tried very hard to say but fell short

Please Login or Register  to view this content.

It isn't quite working the way I intended to explain. To try something, is it possible to remove the ('NI&O Clarity Construction Dump'!$J2=A2)* part? I tried but I am getting lost in the code's parentheticals. I think what I had mentioned in my initial request has more to do with VLOOKUP. What I would be happy settle for is have the just pull each row as they are rather than use a lookup.

Gonna fix and simplify my request statement and see if that helps first.

If value in 'NI&O Clarity Construction Dump'!$J2 contains both text "ISP" and text "Only",
then populate value of "ISP Only",
If value in 'NI&O Clarity Construction Dump'!$J2 contains both text "OSP" and text "Only",
then populate value of "OSP Only",
If value in 'NI&O Clarity Construction Dump'!$J2 contains both text "ISP" and text "OSP",
then populate value of "ISP & OSP",
If value in 'NI&O Clarity Construction Dump'!$J2 is blank,
then pull value in 'NI&O Clarity Construction Dump'!$I2

Forget the 'NI&O Clarity Construction Dump'!$J2=A2 part. I realize that is silly now.