cross referenceing data in excel
 

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?

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.
--
Gary's Student


"Dave O'Connor" wrote:

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?

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dave O'Connor" <Dave wrote in message
...
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?

Hi,
If your ID column is the first column in your data sets then you can
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 in
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 B
then:
=INDEX(Sheet2!A1:A10,MATCH(A1,Sheet2!B1:B10,0))


Hope this helps!
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=474578