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

#### Brownick

##### New Member
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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Here's an idea

Excel 2010
HIJK
10740OK0740, 0111, 0115
20221OK0330, 0310, 0335
30245NO0150, 0740, 0330
41540, 0255, 0221
50310, 0611
Sheet7
Cell Formulas
RangeFormula
I1=IF(ISNA(VLOOKUP("*"&H1&"*",\$K\$1:\$K\$5,1,0)),"NO","OK")

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,

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
HIJK
10740OK0741, 0111, 0115
20151NO0330, 0310, 0335
30255NO0150, 0741, 0330
41540, 0245, 0221
50740
Sheet7
Cell Formulas
RangeFormula
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")

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.

Replies
5
Views
713
Replies
2
Views
3K
Replies
3
Views
1K
Replies
1
Views
427
Replies
10
Views
2K

Threads
1,221,217
Messages
6,158,589
Members
451,501
Latest member
andysacko

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back