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)
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article ,
Gina C <Gina
wrote:
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