cross referenceing data in excel

D

Dave O'Connor

I have two data sets in excel, each with the same unique id. I am trying to
enrich the data from one data set with the other. Therefore if for example I
have name and address details & the unique ID on one sheet AND I have say
annual spend and unique ID on another sheet. I want to combine the data sets
using the unique ID as the identifier and create one whole data set. ie. have
one data set with unique ID, name & Address and spend on one sheet

Can this be done?
 
G

Gary''s Student

VLOOKUP can give you just what you wantSuppose we have a table of pet IDs,
pet names and pet types starting in A1:

100 oscar dog
500 daisey cat
55 rover dog
34 mini goldfish

and another table of pet IDs and pet ages starting in A7:

500 6
100 4
34 1
55 12

To combine the tables in D1 put =VLOOKUP(A1,$A$7:$B$10,2,0) and copy down.
 
M

Max

One way using INDEX and MATCH..

Assume in Sheet1, data is in cols A to C, from row2 down
with unique IDs in col C

Name Add ID
Nam1 Ad1 ID1
Nam2 Ad2 ID2
Nam3 Ad3 ID3
Nam4 Ad4 ID4

In Sheet2, data is in cols A and B, from row2 down
with unique IDs in col B

Spend ID
100 ID1
200 ID2
300 ID3
400 ID4

Let's bring over Spend from Sheet2 into col D in Sheet1

In Sheet1
---------
Put in D2:
=IF(ISNA(MATCH(Sheet1!C2,Sheet2!B:B,0)),"",
INDEX(Sheet2!A:A,MATCH(Sheet1!C2,Sheet2!B:B,0)))

Copy D2 down as far as required, and you'd get:

Name Add ID Spend
Nam1 Ad1 ID1 100
Nam2 Ad2 ID2 200
Nam3 Ad3 ID3 300
Nam4 Ad4 ID4 400
etc

Adapt to suit ..
 
P

pinmaster

Hi,
If your ID column is the first column in your data sets then you ca
use a VLOOKUP, if not then use a combination of INDEX and MATCH.
Say your ID #'s are in column A on the main sheet and your ID #'s i
column A and annual spend data in column B of sheet 2 then:
=VLOOKUP(A1,Sheet2!A1:B10,2,0)

If your annual spend is in say column A and your ID #'s in column
then:
=INDEX(Sheet2!A1:A10,MATCH(A1,Sheet2!B1:B10,0))


Hope this helps!
J
 
Top