Compare Data Between 2 Sheets with Unique Fie [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,
I am working in Excel 2008 for Mac. I am working in 2 sheets and in sheet one I have a persons ID number along with first name, last name, home address and email address (not all email address have been collected). In sheet two I have a persons ID number and email address. What I would like to know, is there a way I can query between sheet 1 and sheet 2 to compare the unique ID numbers of each person and if there is no email address associated in sheet one that it can pull it from sheet 2?

For example
Sheet 1:
ID NAME Address Email
123 Brian Jones 123 Street bjones@123.com
234 Jim Smith 234 Street
345 John Wilson 345 Street jwilson@345.com
456 Dave Harr 456 Street

Sheet 2:
ID Email
123 bjones@123.com
234 jsmith@234.com
345 jwilson@345.com
456 dharr@456.com

Now, keep in mind the sheets are not that neat where I can sort the ID row and just copy paste.

Any help would be greatly appreciated. Thanks

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
793
in D3 of sheet1 against ID 234 (Jim Smith) type this formula

=VLOOKUP(A3,Sheet2!$A$1:$B$100,2,FALSE)

copy this to the blsnk cells in column D in sheet 1

It may be difficult to successively copy to blank cells
you can sort sheet 1 according to column D in which case all the blank email cell rows will be in group and it is easy to copy. if you do not want to sort

copy this formula in D2 even though email is already filled this formula

=VLOOKUP(A2,Sheet2!$A$1:$B$100,2,FALSE)

and copy it down.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!