Excel: Matching a string from a list of strings

Question

I have two lists:

list 1:

gnat
dog
cat
house
dog

list 2:

dog
mouse
flat
cat
cat

I want to check if any of the words in list 2 are in list 1, regardless of where the word exists in the list, so in the end, I should get something like this:

gnat   dog   Yes
dog    mouse No
cat    flat  No
house  cat   Yes
dog    cat   Yes

Does anyone know how I can do this? I've tried the following but have had no luck:

=IF(ISNUMBER(SEARCH(A:A,B1)), "Yes", "No")
=IF(ISERROR(FIND(A:A,B:1)),"Yes", "No")

Gary's Student · Accepted Answer · 2016-05-26 14:07:27Z

4

In C1:

=IF(COUNTIF($A$1:$A$5,B1)=0,"No","Yes")

and copy down.

For dog, you will get "Yes" no matter how many dogs there are in column A.

edited May 26, 2016 at 14:07

answered May 26, 2016 at 14:01

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Forward Ed Over a year ago

Nice Gary! I think I am coming down with a case of Craner-itis! I immediately started thinking of a sumproduct method! You have the better approach!

Gary's Student Over a year ago

@ForwardEd I must agree with you..............I also considered SUMPRODUCT() first

Collectives™ on Stack Overflow

Excel: Matching a string from a list of strings

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related