CODEWITHSUNDEEP
excel
user2503740
user2503740

Reputation: 1

Excel: matching data from two lists

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?

Upvotes: 0

Views: 69

Answers (1)

Jerry
Jerry

Reputation: 71598

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)

Upvotes: 1

Related Questions