Formula to find a value in a column with cells that have multiple values seperated by commas

[Brownick]

Hello all,

I am trying to use a formula to determine if a value on one tab exists within a column on another tab that has multiple values per cell separated by commas. If the value does exist, I would like to return "OK", if not "NO".

For example, I have the value 0740 in cell B13, and the below table is on tab "template" in column K.

0740, 0111, 0115

0330, 0310, 0335

0150, 0740, 0330

1540, 0255, 0221

0310, 0611

<tbody>
</tbody>

The equation I would think to use is =if(vlookup(B13,'template'!A:K,11,FALSE),"OK","NO")
However, I am being returned a #N/A which I'm assuming is because the data is separated by commas.

Does anyone know what equation I should be using for this?

Thank you.

 

[Momentman]

Here's an idea

Excel 2010
	H	I	J	K
1	0740	OK		0740, 0111, 0115
2	0221	OK		0330, 0310, 0335
3	0245	NO		0150, 0740, 0330
4				1540, 0255, 0221
5				0310, 0611
Sheet7

Cell Formulas
Range	Formula
I1	=IF(ISNA(VLOOKUP("*"&H1&"*",$K$1:$K$5,1,0)),"NO","OK")

 

[Brownick]

Momentman,

Thank you for the response. I don't exactly understand the logic behind ISNA, but it does work so thank you.

However, I should have mentioned some of the values are only one number (i.e. 0280). It seems to me this formula returns no if the value is only listed by itself, but will return OK if it is apart of a group.

Any idea how to fix?

Thank you.

 

[Momentman]

Momentman,

Thank you for the response. I don't exactly understand the logic behind ISNA, but it does work so thank you.

However, I should have mentioned some of the values are only one number (i.e. 0280). It seems to me this formula returns no if the value is only listed by itself, but will return OK if it is apart of a group.

Any idea how to fix?

Thank you.

How about this

Excel 2010
	H	I	J	K
1	0740	OK		0741, 0111, 0115
2	0151	NO		0330, 0310, 0335
3	0255	NO		0150, 0741, 0330
4				1540, 0245, 0221
5				0740
Sheet7

Cell Formulas
Range	Formula
I1	=IF(COUNTIF($K$1:$K$5,"*"&H1&"*")>0,"OK","NO")
I2	=IF(COUNTIF($K$1:$K$5,"*"&H2&"*")>0,"OK","NO")
I3	=IF(COUNTIF($K$1:$K$5,"*"&H3&"*")>0,"OK","NO")

 

[Brownick]

Momentman,

Thank you for your assistance. Both of the equations you have provided work. I realized my issue with the first one was the way my cells were formatted (excel read 0150 as 150).

Thank you.