Hi Domenic
I think I have it - as I put in my last post to Boggled:
=INDEX(((Date)*(Name="Joe")),MATCH(MIN(ABS(((Date) *(Name="Joe"))-TARGET)),ABS(((Date)*(Name="Joe"))-TARGET),0))

where Name is the range of Names (A1:A4)
Date is the range of Dates (B1:B4)
And Target is the lookup date you are searching for.

I also tested it with exact date match, no problem. Thanks for your input
as well!

Mike

"Domenic" wrote:


Domenic Wrote:
Try the following array formula that needs to be entered using
CONTROL+SHIFT+ENTER...

=INDEX(B2:B10,MATCH(MIN(IF((A2:A10=D2)*(ABS(B2:B10-E2))<0,(A2:A10=D2)*(ABS(B2:B10-E2)))),(A2:A10=D2)*(ABS(B2:B10-E2)),0))

...where D2 contains the name of interest and E2 contains the date of
interest.

Hope this helps!


My formula fails when the the look up date exactly matches a date
within the range of dates, returning the closest match other than that
look up value itself.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273186