Lookup with several criteria: VBA needed?
 

Dear all,
I am trying to merge datasets for statistical purposes and run into
trouble. Two sheets are involved with columns as follows:


Sheet 1
Company Individual Date

Sheet 2
Company Individual Status Date_Start Date_End


Sheet 1 lists individuals, associated with companies, at various times.
From Sheet 2 I want to extract the status of that individual as
specified for a given date range.

Most of the lookup discussions on the internet discuss the exact
matching of one or more criteria. Before extracting Status, I need to
check that the company and individual
match as well as ensuring that the date falls within the correct
range.
The Lookup function appears to stop at the first match, hence nesting
statements is difficult. Can VBA solve my problem? I am comfortable
with Excel but I'm quite new to VBA!

//Thomas, grateful student!



Example:
------------
Sheet 1
Company Individual Date
BOEING ADAM 20050115
BOEING ADAM 20050205
COKE ERIC 20050205
COKE CHRIS 20050603

Sheet 2
Company Individual Status Date_Start Date_End
BOEING ADAM Manager 20050101 20050120
BOEING ADAM Owner 20050125 20050302
COKE ERIC Assoc 20050201 20050810
COKE CHRIS Assoc 20040620 20060101

I would like the output on sheet 1 to become:

Sheet 1
Company Individual Date Status
BOEING ADAM 20050115 Manager
BOEING ADAM 20050205 Owner
COKE ERIC 20050205 Assoc
COKE CHRIS 20050603 Assoc