searching a lookup table with multiple values in a field

[raysonjoberts]

I have a csv file that that I am using for a lookup which has multiple values in a particular field. I am trying to do a lookup which matches any one of the field values.

example:

lookup table file -
room,color
livingroom,purple|green|yellow

(the pipe symbol delineates the different values in the color field)

Then my search -

<base search> 
| lookup paint_colors room OUTPUTNEW color
| search color=purple
| fields room,color
| stats list by room


My desired result would be to see livingroom in the results. Is it possible to search for any one value in a field with multiple values?

Thanks in advance!

[ITWhisperer]

<base search> 
| lookup paint_colors room OUTPUTNEW color
| eval match=if(match(room_color,color),"matched",null())
| where match="matched"
| fields room,color
| stats list by room

[kamlesh_vaghela]

@raysonjoberts 

Try where for searching

| where like(color,"%purple%")

KV

[raysonjoberts]

Thanks KV. 
I think I have over simplified my question.
What I am really after is for each value in the lookup table field to be recognized as individual values and not a single string. So for example, a different search might be

<base search>
| lookup paint_colors room OUTPUTNEW color
| stats count by room,color

And my desired outcome would be

livingroom purple 1
livingroom green 1
livingroom yellow 1

[kamlesh_vaghela]

@raysonjoberts 

try this

<base search>
| lookup paint_colors room OUTPUTNEW color
| where like(color,"%purple%") 
| eval color=split(color,"|")
| stats count by room,color

KV