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.

Hi,
Try the following input as an array formula (Shift-Ctrl-Enter):
=IF(Name="JOE",INDEX(Date,MATCH(MIN(ABS(Date-TARGET)),ABS(Date-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.

HTH

"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.

Sorry,
upon further testing, I found an error, in that it indiscriminately returns
the closest date. Will keep trying, if I find anything will post back for you

"mzehr" wrote:

Hi,
Try the following input as an array formula (Shift-Ctrl-Enter):
=IF(Name="JOE",INDEX(Date,MATCH(MIN(ABS(Date-TARGET)),ABS(Date-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.

HTH

"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.

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.

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!


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

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

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

mzehr Wrote:
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



Hi Mike!

Yes, your formula seems to work fine. When I discovered the problem
with my formula, I tested it against yours and found it returned the
correct results. :)

Cheers!


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

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.

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.

Hi Boggled.
I set up my worksheet 1 and 2 as you described.
In Sheet1 I have the following table (along with the desired results):

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

I named the ranges on worksheet 2 as follows:
Name =Sheet2!$B$2:$B$8
Date =Sheet2!$A$2:$A$8
Note that you do not have to name the ranges, you could just refer to the
cell references. I think it makes the formula cleaner, shorter and easier to
follow.

The formula that I entered on Sheet 1 in D2 as an array formula
(Shift-Ctrl-Enter) was:
=INDEX(((Date)*(Name=A2)),MATCH(MIN(ABS(((Date)*(N ame=A2))-B2)),ABS(((Date)*(Name=A2))-B2),0))
I then copied that down through D6 and voila, got the results shown.

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.

Hi Boggled.
I set up my worksheet 1 and 2 as you described.
In Sheet1 I have the following table (along with the desired results):

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

I named the ranges on worksheet 2 as follows:
Name =Sheet2!$B$2:$B$8
Date =Sheet2!$A$2:$A$8
Note that you do not have to name the ranges, you could just refer to the
cell references. I think it makes the formula cleaner, shorter and easier to
follow.

The formula that I entered on Sheet 1 in D2 as an array formula
(Shift-Ctrl-Enter) was:
=INDEX(((Date)*(Name=A2)),MATCH(MIN(ABS(((Date)*(N ame=A2))-B2)),ABS(((Date)*(Name=A2))-B2),0))
I then copied that down through D6 and voila, got the results shown.

Hope this helps

"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.

Sorry for multiposts I got an error saying the post didn't work. Should have
doublechecked before reposting

"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.

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.

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.