Custom Vlookup in MS Excel 2007 1

[BxWill]

Have a Excel workbook with three worksheets. Sheet 1 contains 19 columns as displayed below. Note the columns beginning with "CustomerID" repeats. Sheet 2 contain the data that is used to populate columns B through J on Sheet 1. Sheet 3 contain the data that will be used to "auto populate" columns K through S on sheet 1.

I am using the worksheet to select 250 customers for the treatment group and 250 customers for the control group in preparation for a scientific study. Sort of doing this on a customer by customer basis as part of a presentation.

What I would like to do is upon entry of the "CustomerID" in column B on Sheet 1, columns C through J will be populated via a vlookup from worksheet 2 and I would like columns K through S "auto populate" with data from Sheet 3 that is similar to the data that is in columns C through J.

In other words, I need to ensure that the customers in my treatment and control groups are as similar as possible in terms of age span, End Date, Gender, Product, same zip code, and duration (i.e. 12 months, 9 months, etc.).

Is this possible? Any insight as to how it can be accomplished will be greatly appreciated.

Can you think of a more efficient way to accomplish the objective?

Thanks in advance.

Number (column A)
CustomerID (column B)
EndDate (column C)
Score (column D)
Age (column E)
Gender (column F)
Product (column G)
County (column H)
Zip (column I)
Duration (column J)
CustomerID (column K)
EndDate (column L)
Score (column M)
Age (column N)
Sex (column O)
Product (column P)
County (column Q)
Zip (column R)
Duration (column S)

 

[SkipVought]

Hi,

Please post some sample data and explain the algorithm for relating the data.

Please make the sample data relevant to your explanation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

For instance, could there be a one to one relationship between customers, or one to many, or many to many?

Under certain circumstances, a query might work to produce results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Will explore the query route and join on those fields that should be the same - age, EndDate, Gender, etc and then post back.

Basically, would like a query to select 250 records for a particular customer (customer A) and then select 250 records for all other customers (say, customer B,C, D, etc.) that will have the same age, EndDate, Gender, Zip, County, etc. as the first 250 records for customer A.


sample data below;



Number (column A) --1
CustomerID (column B)---25689
EndDate (column C) --12/31/2010
Score (column D) --12.3
Age (column E) --25
Gender (column F) --M
Product (column G)--CARE
County (column H) --Anderson
Zip (column I) --44509
Duration (column J) --12
CustomerID (column K) 25690
EndDate (column L) --12/31/2010
Score (column M) --12.3
Age (column N) --25
Gender (column O) --M
Product (column P) --CARE
County (column Q)--Anderson
Zip (column R)44509
Duration (column S)--12

 

[SkipVought]

Query would work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}