Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 28th 04, 02:19 PM
Boggled Excel User
 
Posts: n/a
Default lookup in colum a and compare values in colum b

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.


  #2   Report Post  
Old October 28th 04, 03:01 PM
mzehr
 
Posts: n/a
Default

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.

  #3   Report Post  
Old October 28th 04, 03:09 PM
mzehr
 
Posts: n/a
Default

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.

  #4   Report Post  
Old October 28th 04, 04:05 PM
mzehr
 
Posts: n/a
Default

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.

  #5   Report Post  
Old October 28th 04, 04:14 PM
Domenic
 
Posts: n/a
Default


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



  #6   Report Post  
Old October 28th 04, 05:08 PM
Domenic
 
Posts: n/a
Default


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

  #7   Report Post  
Old October 28th 04, 05:35 PM
mzehr
 
Posts: n/a
Default

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


  #8   Report Post  
Old October 28th 04, 05:57 PM
Domenic
 
Posts: n/a
Default


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

  #9   Report Post  
Old October 28th 04, 11:15 PM
Boggled Excel User
 
Posts: n/a
Default

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.

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

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 11:43 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017