Formula for classifying data

Hi everyone! I'm doing some preliminary work for an academic project. I'm trying to measure the diversity of social networks in a part of the world where ethnicity can be identified by surname. I'm using publicly available data from Facebook. I've been doing this for a while using a cumbersome method, but I suspect that there may be a simpler solution using excel. Unfortunately, I haven't found an approach that works yet, hence the post here. Let me briefly explain what I'm trying to do -- I've attached a file here as an example.

I have one spreadsheet for each study subject. The country I'm doing this work in has three main ethnic groups. In column A, I have the 100+ most common surnames from ethnic group A. In column B, I have the 100+ most common surnames from ethnic group B, and in column C, the 100+ most common surnames from ethnic group C.

In column D, I paste the names of the subject's social network (in other words, everyone on their friend list). What I want to do is classify the ethnicity of each friend. To minimize manual work, the first cut would be to determine whether the names in column D appear in columns A, B, or C.

In short, I need a formula that will mark a "1" (or something similar) when a name in column D appears in column A (I'll use the same formula to check for matches with columns B and C), and "0" when it doesn't. The slight complicating factor is that columns A, B, and C have only surnames, while D will have surnames, given names, and nicknames. So the formula should code a match if one of the names in column D is match with A, B, or C (in other words, the cell contents will never be a complete match, because column D will never have only surnames).

The formula should produce the following using the sample file:

D1 should be a column A match
D2 a column C match
D3 no match
D4 a column B match
D5 a column A match

I'd really appreciate any help on this. I've spent quite a while looking for solutions, but nothing has worked reliably. Thanks very much in advance!

You need to be more consistent with your definition of surname - this formula in E1 will work for the first 3:

=IF(ISNUMBER(MATCH(TRIM(RIGHT(D1,LEN(D1)-FIND(" ",D1))),A:A,0)),1,IF(ISNUMBER(MATCH(TRIM(RIGHT(D1,LEN(D1)-FIND(" ",D1))),B:B,0)),2,IF(ISNUMBER(MATCH(TRIM(RIGHT(D1,LEN(D1)-FIND(" ",D1))),C:C,0)),3,0)))

but it fails on the 4th name as you have 2 spaces (and thus 3 names) and it fails on the 5th name as it is the first of the 3 names.

Anyway, hope this helps.

Pete

Thanks for the quick reply, Pete.

Unfortunately I can't be more consistent with the order of names -- we'll be going through thousands of them, so the manual work would be overwhelming. In addition, it may often not be clear which of the names is the surname.

Is there anyway that we can code a match is any of the names from column A, B, or C appear in D? That should be sufficient and shouldn't produce many errors.

Thanks again!