Lookup list in column in another column with slightly different information

[fastchase]

Hello,

I have 2 columns, A and B.

B has a list of IP addresses: 182.71.145:8080 for example (not a real IP)
A has a list of Username:Password:IP address

I need to find out if the IP addresses from column B exist within column A.

Can I do this with conditional formatting? It doesn't work if I do "find duplicate values" because obviously the values in both columns don't match exactly.


Example

Column A Column B
JohnDoe:password1:182.71.145:8080 183.71.145:8080
JaneDoe: Password2:183.71.145:8080 182.71.145:8080

[cbatrody]

Hi,

Try the following in column C, see the file attached!

=IF(ISERROR(INDEX(A$1:A$3,MATCH("*"&B2&"*",A$1:A$3,0),1)),"No Match","Match Found")

[lookingforhelp1]

How about this?


Column A1
Username:Password:182.71.145:8080

Column B1
182.71.145:8080

Column C1
=IF(SUMPRODUCT(--ISNUMBER((SEARCH(B1,A1)))),"Match","")

[fastchase]

Both solutions worked great thank you!!!

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.