lookup in colum a and compare values in colum b

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Hi again,
Try this, I have tested it with the data you submitted, and it appears to
wor
=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.
 
Try the following array formula that needs to be entered usin
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 o
interest.

Hope this helps
 
Domenic said:
Try the following array formula that needs to be entered usin
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 o
interest.

Hope this helps!
Click to expand...


My formula fails when the the look up date exactly matches a dat
within the range of dates, returning the closest match other than tha
look up value itself
 
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
 
mzehr said:
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 you
input
as well!

Mike
Click to expand...

Hi Mike!

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

Cheers
 
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
 
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)*(Name=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
 
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)*(Name=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
 
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)*(Name=A2))-B2)),ABS(((Date)*(Name=A2))-B2),0))
I then copied that down through D6 and voila, got the results shown.

Hope this helps
 
Sorry for multiposts I got an error saying the post didn't work. Should have
doublechecked before reposting
 
I'm not sure how the named ranges part works, but when I entered it manually
it worked great!

Thanks Mike!
 
Thanks for the feedback. Glad to help.
Mike

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

Thanks Mike!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match or Lookup formula help please 6
If and Vlookup 3
How do I sum cells by refering to discrete values? 7
Row and Col Index of a cell with Max Value 3
Cross Referencing two data worksheets and desciphering differences 1
Project a date and calculate a % in one formula 4
comparing columns in two spreadsheets 1
Checking data in 2 columns against a further 2 columns.... 1

Back
Top