Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old October 29th 04, 02:08 PM
mzehr
 
Posts: n/a
Default

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.


  #12   Report Post  
Old October 29th 04, 02:08 PM
mzehr
 
Posts: n/a
Default

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.

  #13   Report Post  
Old October 29th 04, 02:18 PM
mzehr
 
Posts: n/a
Default

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.

  #14   Report Post  
Old October 29th 04, 06:10 PM
Boggled Excel User
 
Posts: n/a
Default

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.

  #15   Report Post  
Old October 29th 04, 06:38 PM
mzehr
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:53 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017