Returning nearest past date for a person?

  • Thread starter Thread starter bb67dart
  • Start date Start date
B

bb67dart

Here's a layout of data in table 1:

Name Screening Date
Dave 1/15/2009
Dave 2/3/2009
Sam 3/5/2008
Sam 6/23/2008
.....

and in table 2:

Name Intervention Date
Dave 1/21/2009
.....
___________

I am trying to add a formula to table 2, which will look up, for Dave,
the closest screening date before the intervention date. The answer
should be 1/15/2009, since it's for Dave and is not greater than the
intervention date of 1/21/2009.

I am not sure how to combine criteria when looking things up like
this.. any help is appreciated, thank you.
 
Nevermind, I forgot that this could be done with index/match:
=INDEX($D$2:$D$4,MATCH(1,(($C$2:$C$4=C10)*($D$2:$D$4<E10)),0))
 
Back
Top