Compare 2 spreadsheets

[cmitch]

Hopefully this isn’t too confusing…

I have two spreadsheets containing several thousand rows each and need to compare the two. The first (AV) is from our internal database and the second is from an online source (XYZ).

AV contains: Name, Email, Phone, Status
XYZ contains: Name, Email, Phone, Source​

I need to see which items in XYZ are also in AV. On each match, I need the “Status” (pulled from AV).


Thank You in advance for any insight!

 

[Special-K99]

Assuming all email address fields are filled in you need only match on the email since an email address is unique.
Possibly the same goes for phone number.

Maybe
in XYZ!A1
=VLOOKUP(XYZ!B1,AV!B$1:D$100000,2,0)
and copy down the column

 

[cmitch]

Assuming all email address fields are filled in you need only match on the email since an email address is unique.
Possibly the same goes for phone number.

Maybe
in XYZ!A1
=VLOOKUP(XYZ!B1,AV!B$1:D$100000,2,0)
and copy down the column

This worked - thank you!

If I throw another sheet in the mix (LMN) - is there a way to show if someone is on only XYZ or LMN or they're on both?

 

[Special-K99]

Assuming LMN has the same format (you havent specified that)

in a blank cell
=VLOOKUP(XYZ!B1,LMN!B$1:D$100000,2,0)

This will give you items on XYZ that are in LMN