How to find 1 value in a range of other large values

[Brandonmac777]

Hello forum, first time poster long time reader.

I'm looking for a formula that would tell me if a value in colume E can be found in column M while the values in column M may have characters before and after the value I'm looking for. The values are alpha and numberic in many cases.

Find E2 in M:M where E2 = 0123 and is in M:M as a15012388. The "yes/no" result is marked in red where I would like to have the answer.

Thank you very much for any help in advance!

			Description	Looking For	Yes/No						Where Is It?
			Apples	0151003							65403213
			Oranges	0151004							35465740
			Pears	0151005							3243240
			Bears	0151006							16874
			Funny Stuff	0151007							as6540ad5f4
			That I	0151008							16540
			Wish I Could	0151009							654tts3
			Type	0151010							ad6f54604da
			Apples	0151011							3a5d4f6450
			Oranges	0151012							ad6f54654
			Pears	0151013							d6546574652d
			Bears	0151014							6546574fss
			Funny Stuff	0151015							gt0151003abc
			That I	0151016							adf6543
			Wish I Could	0151017							dfa 6543
			Type	0151018							a df adf6 54
			Apples	0151019							adf ad654
			Oranges	0151020							adf54-654
			Pears	0151021							654-sdf-sdf
			Bears	0151022							adf654-s
			Funny Stuff	0151023							asdf6
			That I	0151024							654654ds
			Wish I Could	0151025							sdf654
			Type	0151026							sdf4567

<TBODY>
</TBODY>

 

[Rick Rothstein]

Put this formula in E2 and copy it down...

=IF(COUNTIF(M$2:M$25,"*"&E2&"*"),"Yes","No")

Adjust the M$2:M$25 range to cover all your data in Column M, but retain those $ signs in front of the row numbers. You could do this...

=IF(COUNTIF(M:M,"*"&E2&"*"),"Yes","No")

but the first version should be more efficient.

 

[konew1]

Rick's reply will highlight the row if the item in E:E is found in M.
This will highlight the row where it occurs in M
=SEARCH("*"&E$2&"*",M2) in row 2 and copy down

 

[Rick Rothstein]

Put this formula in E2 and copy it down...

=IF(COUNTIF(M$2:M$25,"*"&E2&"*"),"Yes","No")

Adjust the M$2:M$25 range to cover all your data in Column M, but retain those $ signs in front of the row numbers. You could do this...

=IF(COUNTIF(M:M,"*"&E2&"*"),"Yes","No")

but the first version should be more efficient.

I meant to say... "Put the formula in F2 and copy down..."

 

[Brandonmac777]

Works very well. Thank you both very much.

I toyed around with =IF(ISERROR(MATCH("*"&E2&"*",$B:$B, 0)), "1", "0") and came up with basically the same thing. I had moved some stuff around so the column was different. Again.. Thank you very much.