Combining Separate Tables With Multiple Values

Here's my issue.

I have 2 spreadsheets. One has names, phone numbers, and addresses. The second has names and another number(Let's call it ID Number). The problem is that the first one has over 14000 entries, while the 2nd one has around 600. All the names in the 2nd table are also in the first one, and so I want to add all the ID numbers to all the matching entries in the 1st table, based on name.

I've been trying to do this with VLOOKUP, and I can't seem to do it, and feel like an idiot. Can anyone help me out?

Let's say your first table is on sheet1 with Name in the first column beginning in row 2.
Your second table is on Sheet2 with Name in the first column beginning in row 2 and ID in Column B.

Add a column on Sheet1 for id and in row 2 dragged down
=VLOOKUP(A2,Sheet2!$A$2:$B$1000,2,FALSE)
If your names are exact matches, that should work for you.

Are the names in one column or two? Is it the same on both sheets? Could you attach a sample workbook? You don't have to keep all 14,000 rows on the first sheet, and you should replace the phone numbers and addresses with dummy data. You could just use something like 555-555-5555 for the phone number and 123 Mockingbird Lane for the address and copy them down the entire list.

In E2 and copied down,

=INDEX(Sheet1!C1:C1000, MATCH(Sheet2!A2 & Sheet2!B2, Sheet1!A1:A1000 & Sheet1!B1:B1000, 0))

The formula MUST be confirmed with CTrl+Shift+Enter, not just Enter.

No Dice. It seems to work in the example sheet I uploaded, but not in my sheet. I'm going to upload the one I'm actually working with...I really appreciate the help.

I also consolidated the last and first names, in case that makes things easier.

Nevermind privacy, I had to delete most of the information just to get the thing small enough to upload.

http://www.2shared.com/document/BCyu...s_Upload_.html

SHG's formula should work. I cannot assess the other site. Can you upload a zipped copy to this site? (Go Advanced>Manage Attachments).

Originally Posted by ChemistB

SHG's formula should work. I cannot assess the other site. Can you upload a zipped copy to this site? (Go Advanced>Manage Attachments).

Sure! Here you go. I also had to cut the list in half to get it small enough.

Okay, first, adjusting it for your workbook.......
=INDEX('Sheet 1'!$B$2:$B$1000, MATCH(A2, 'Sheet 1'!$A$2:$A$1000, 0))
Since you combined first and last names, you don't need to use CNTRL SHFT ENTER and it's simplified. Also, Sheet1 (and 2) became 'Sheet 1' because your workbook has spaces in there. This still end up giving #NA's because on Sheet 1, you have a double space between the last and first name. Just select column A, CNTRL H to bring up the Search and Replace and "Find What" type in double space, "Replace with" type in single space and replace all. Does that work for you now?

As per your request regarding respecting data confidentiality, I hav eremoved the attachment.