I appreciate your help! However when your formula is input it gives me #NUM
errors. I believe I must be targeting the incorrect cells/ranges.

DATE=range of dates that are searched
TARGET=cell containing date desired
NAME=range of names

You listed "JOE" in the formula. Would a Cell reference deter this formula
from working? Also the references are on different worksheets within the
same excel file.

let me see if i can draw a better picture.

worksheet 1
NAME Transaction Date Return Amt. Closest puchase
Joe 10/16/2004 -1
Joe 5/17/2004 -2
Joe 5/02/2002 -4
Mary 1/8/2004 -50
Mary 3/12/2004 -2


Worksheet 2
Name Date Purchase Amt
Joe 10/12/2001 3
Joe 10/11/2003 4
Joe 09/01/2004 2
Joe 01/02/2003 6
Mary 01/01/2003 5
Mary 10/04/2004 6
Mary 12/25/2001 5


I'm trying to match the closest date in worksheet 2 to the transaction date
in worksheet1.

Did this give you enough information? Does the previous formula work in
this situation? I could not use it for this purpose.

-boggled


"mzehr" wrote:

Hi again,
Try this, I have tested it with the data you submitted, and it appears to
work
=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.


"Boggled Excel User" wrote:

How do you look up a value in colum a and find the nearest match in colum b.

Colum A Colum B
Joe 10/16/2004
Joe 5/17/2004
Mary 1/8/2004
Mary 3/12/2004

If I had a name in another worksheet and was looking for the name & date
that most closely matched it. (joe 10/01/2004). vlookup just gives the
first one. I don't need to have both dates returned.