Excel: matching data from two lists

Question

In sheet1, collumn A, I have a list of names.

Let's say they are all first, middle, and last names of different people I have listed as friends on FaceBook. We will pretend I am very popular and have 10,000 friends and they all have different names. I want to identify many common names.

In sheet2, I have compiled a list of the top 50 names.

I want to create a function in sheet1, collumn B, which matches the names in sheet1, collumn A, with the "common names" list from sheet2.

I have been able to search for a name in particular using an equation like

=IF(ISNUMBER(SEARCH("*John*","common","")

This way, I have been able to label all of my friends who have the name John either as a first, middle, or last name. I have also been able to use a similar function:

=IF(ISNUMBER(SEARCH(Sheet2!$A$2,"common","")

so that no matter which name is in A2 in sheet2, I can drag this function down and identify everyone in my friends list who has that name in their name.

I want to find a quick way to apply a similar function for EVERY common name without having to (a)write multiple equations or (b)write one very lengthy equation.

How to solve this problem?

I think it can be done with vlookup

Andrew
– Andrew

2013-06-20 07:37:14 +00:00
Commented Jun 20, 2013 at 7:37 — Andrew
– Andrew, Commented Jun 20, 2013 at 7:37

Jerry · Accepted Answer · 2013-06-20 08:23:20Z

1

If I'm well understanding what you want to obtain as results, you could use this formula in sheet2 B2 and drag it down your list of 50 common names:

=VLOOKUP("*"&A2&"*", Sheet1!A:A, 1, 0)

answered Jun 20, 2013 at 8:23

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

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

Collectives™ on Stack Overflow

Excel: matching data from two lists

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related