Combine 2 worksheets & fill in missing data

I searched for a couple hours on how to figure this out but no real luck. Here's the situation:

I have 2 sheets in the same workbook (sheet 1 & 2) with one matching column (A) of info and need them to combine and fill the missing data. Each sheet has identical column heading and the amount of data is not the same; sheet 1 has ~2000 rows and the other ~5000. Sheet 1 is consolidated so I would like to fill in the missing cells from sheet 2.

Ex. Sheet 1

Email Last First Title Company Contact
1 [email protected] x Bob x x SPAM

Ex. Sheet 2

Email Last First Title Company Contact
13 [email protected] Jones Bob Engineer ABC x

As you can see, the missing cells in sheet 1 could be filled from the data in sheet 2; also note that the above info is on different rows (and will be random for each case). I only want the columns to fill in that have a matching email, the other 3000 rows in sheet 2 do not match with sheet 1 and I don't want the rest of it to transfer over.

Hopefully this is enough info to help. Thanks in advance!

Please Login or Register  to view this content.

Can you post up your sample workbook? Click GO ADVANCED and use the paperclip icon to post up your workbook.

Based on that sample, put this in Sheet1 B2 and copy down and across:

=IF(ISNUMBER(MATCH($A2, Sheet2!$A:$A, 0)), INDEX(Sheet2!B:B, MATCH($A2, Sheet2!$A:$A, 0)), "")