Combine two sets of data

Hey all,

I'm stuck on something and was hoping you guys can help me. Say I got information from two different sources. One has First Name Last Name and SS# and the other has First Name Last Name and Occupation. Both are completely out of order and there is no 1-1 relationship so there are more names in one category then the other. Regardless, I want to combine what does matchup together so it shows up as First Name, Last Name, SS# and Job all on one line. For arguments sake let's say all the names match up exactly and there are no duplicate names. The data would be in one spreadsheet and look kinda like this:

Please Login or Register  to view this content.

So if the first name and last name match in either group I want the data combined. In the above example the data would come out to be:

Please Login or Register  to view this content.

Any ideas?

Originally Posted by ChemistB

I'd make a "dummy column" for the second list to combine 1st and last names. Lets' say list 2 is in F2:H5, in I2 = F2&G2

Then in D2,
=IFERROR(INDEX($H$2:$H$5,MATCH(A2&B2,$I$2:$I$5,0)),"")
Does that work for you?

Alternately you could use an array formula but depending on your list sizes, that might slow down your worksheet.

I could not get that to work. Are you doing it under the assumption that the left data is in A2:C7? I tried it under those conditions and plugged it into D2 and downward and it showed up blank.

Never mind got it working, thanks!