Compare Data Between 2 Sheets with Unique Fie Closed

Question

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

venkat1926 · Answer

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.

Compare Data Between 2 Sheets with Unique Fie

1 response

Subscribe To Our Newsletter!