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

Brandonmac777

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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.

Last edited:
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

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..."

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.

Replies
3
Views
321
Replies
4
Views
437
Replies
3
Views
378
Replies
1
Views
200
Replies
1
Views
446

1,196,508
Messages
6,015,615
Members
441,906
Latest member
gafoor

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.

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

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