How to match comma separated values in cell to a list? Help pls..

[adiritz]

For every number in Column A, I have a separate list. I would like to check if in next 4 rows, any number came from this list.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]COLUMN C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12,1,3,9,4[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1,33,54,99,33[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]45,76,88,1[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12,1,3,9,4[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2,51,31,45,67[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]22,17,19,9[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]22,25,89,99[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2,51,23,45,67[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1,3,45,67[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
















Please advice how to calculate this. I tried Match and Find, not getting any success.

 

[Noodleski]

Hi,

I don't understand how you calculate the TRUE/FALSE column in your example.
In line 2, you want to check for value 1 in the next four rows. I think there is 1 in the row below (last value) and yet you mark 'FALSE' is a result?

 

[Marcelo Branco]

Maybe this formula in C1 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH(","&A2:A5&",",","&SUBSTITUTE(B1," ","")&",")))

Hope this helps

M.