vlookup using match and date range

[Gina C]

I have a list of data in which A:A lists names B:B lists date of status
change and C:C lists the person's status as of the date in B. I need to find
the status for each person listed in column A as of 09/30/07, as of 09/30/08,
and as of 09/30/09). Any suggestions?

A B C
John Smith 04/01/88 Available
John Smith 04/30/97 Active
Jane Doe 09/28/07 On Leave
Jane Doe 10/02/07 Active
Jane Doe 11/07/07 Available
Jane Doe 01/08/08 Active
Polythene Pam 07/27/05 Available
Polythene Pam 08/23/05 Active
Polythene Pam 03/30/06 Resigned

Many thanks!

Gina

 

[Domenic]

Assumptions:

A2:A10 contains the name

B2:B10 contains the date

C2:C10 contains the status

E2 contains the John Smith

E3 contains Jane Doe

E4 contains Polythene Pam

F1 contains 9/30/07

G1 contains 9/30/08

H1 contains 9/30/09

Formula:

Since the data is sorted by date, in ascending order, for each name,
try...

F2, copied across and down:

=LOOKUP(2,1/(($A$2:$A$10=$E2)*($B$2:$B$10<=F$1)),$C$2:$C$10)

If the data can be sorted first by name, in ascending order, and then by
date, in ascending order, the following should be faster...

F2, copied across and down:

=VLOOKUP(F$1,INDEX($B$2:$B$10,MATCH($E2,$A$2:$A$10,0)):INDEX($C$2:$C$10,M
ATCH($E2,$A$2:$A$10,1)),2,1)