Thanks for the feedback. Glad to help.
Mike

"Boggled Excel User" wrote:

I'm not sure how the named ranges part works, but when I entered it manually
it worked great!

Thanks Mike!

"mzehr" wrote:

Hi Boggled,
I just set up my two worksheets with the same information you gave me. On
Sheet 1 I put in the following table and the results I got:

NAME Transaction Date Return Amt. Closest Purchase
Joe 10/16/04 -1 09/01/04
Joe 5/17/04 -2 09/01/04
Joe 5/2/02 -4 10/12/01
Mary 1/8/04 -50 10/04/04
Mary 3/12/04 -2 10/04/04

The formula that I entered (as an Array formula - Shift-Ctrl-Enter) in D2 is
as follows:

=INDEX(((Date)*(Name=A2)),MATCH(MIN(ABS(((Date)*(N ame=A2))-B2)),ABS(((Date)*(Name=A2))-B2),0))
I then copied the formula down through D6

The Named Ranges (Make sure you name these before you put in the array
formula)on Sheet2 are
Date =Sheet2!$B$2:$B$8
Name=Sheet2!$A$2:$A$8
and voila! it works.
Note that you do not even have to name the ranges, it just makes the formula
simpler. You could just enter in the range reference if you wanted to.

Hope this helps.

Mike

"Boggled Excel User" wrote:

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.