Return name in Column J if Column L contains value

[Koplin]

Hi all,

I am looking to get a return of a name from column J if column L contains the value "C". I've set up an index function that works fine if column L contains ONLY "C"...but the problem is sometimes column L contains text like "C, RW". I need to tweak the equation I have to return if column L contains, but limited to "C".

Here is the picture of what I got so far. Below is my equation I am using, with A1 as my value to search for in Column L ("C").

=IFERROR(INDEX('2018-19 Z'!$J$7:$J$598, SMALL(IF('2018-19 Z'!$L$7:$L$598=$A$1, ROW('2018-19 Z'!$J$7:$J$598)-ROW($L$7)+1), ROW(1:1))),"")

Thanks in advance!

 

[oldbrewer]

test for len = 1 ?

 

[Koplin]

I need to tweak the equation I have to return if column L contains, but NOT limited to "C".

Edit. It needs to return ALL values that have C in it, not just limited to "C"

 

[Koplin]

I made a mistake, I need the returns to be all lines WITH "C" in column L. So the Len won't work sadly but thanks!