PDA

View Full Version : Lookup for Next (not last) Date



zest1
03-24-2006, 09:04 PM
VLookups on dates return the last date found, but how does one get the 'next' date?

For example:
Lookup:
1/17/06

Table:
1/1/06
1/31/06
...

returns: 1/31/06

HaHoBe
03-25-2006, 12:22 AM
Hi, zest1,

the array formula should do the job ({} not to be added but to be created on leave of formula by pressing CTRL+SHIFT+ENTER), search date in cell A1, fomukla in B1 on Lookup:
{=INDEX(Table!A1:A100,MATCH(MIN(IF(Table!A1:A100>=A1,Table!A1:A100)),Table!A1:A100,0))}
Formula will not work with a column?s whole range.

Ciao,
Holger

zest1
03-25-2006, 01:02 AM
Thanks Holgar.

Your formula doesn't work for what I'm trying to do. Sorry, perhaps I didn't explain it clearly. I just want to do a simple lookup which returns the larger value in case there are no exact matches.

so if A2 = 1/17/06

D | E
1/1/06 | pencil
1/31/06 | pen

result in B2:C2 = 1/31/06 | pen

The date 1/17/06 is closer to 1/31/06 than 1/1/06, so that's the one I want returned, unless of course there was a record with the exact matching date of 1/17/06, then that is the one I'd want returned.

Thanks again

HaHoBe
03-25-2006, 01:42 AM
Hi, zest1,

sorry for mixing up the sheet names and it seems I can?t be of any help as the formula does the job in Excel97.

Ciao,
Holger

mdmackillop
03-25-2006, 02:27 AM
Hi Zest
Here's a VBA solution to be entered as a User Defined Formula (UDF). Paste the function in a Standard module, enter =GetDate(C3,G1:G20) in any cell where C3 is your date and the G1:G20 is your range.
You can return the "pencil" with a simple vlookup.
This code will work with unsorted dates.
Regards
MD


Option Explicit
Function GetDate(Dte As Date, Data As Range)
Dim Dif1 As Long, Dif2 As Long, d As Range
Dif2 = 100
For Each d In Data
If d <> "" Then Dif1 = d - Dte
If Dif1 = 0 Then
GetDate = Dte
Exit Function
End If
If Dif1 < Dif2 And Dif1 > 0 Then Dif2 = Dif1
Next
GetDate = Dte + Dif2
End Function

zest1
03-25-2006, 02:27 AM
Oh, I see. Thanks a lot, Holgar. :)

The file helped, as I did not have it as you had set it, but yes it works. Can you make one change though, and again my appoligies for not being more clear.

The test date is 1/17/06, and it returns 2/12/06, which is the closest next (or higher) date, even though the previous date 1/15/06 is actually closer (only 2 days away). What I was looking for was to have the closest date returned (in this case, the higher value), and thus bypass the lookup default of always returning the lower value. I only want the lower value if it is in fact closer than the higher value.

Thanks again

HaHoBe
03-25-2006, 02:50 AM
Hi, zest1,

I?m not sure an array formula can deliver (but I?d like to be shown ;))

Better take MD?s UDF but a little bit modified:


Function GetDate2(Dte As Date, Data As Range)
Dim d As Range
Dim Dif1 As Long, Dif2 As Long
Dif2 = 100
For Each d In Data
If d <> "" Then Dif1 = d - Dte
If Dif1 = 0 Then
GetDate2 = Dte
Exit Function
End If
If Abs(Dif1) < Abs(Dif2) Then Dif2 = Dif1
Next
GetDate2 = Dte + Dif2
End Function
Ciao,
Holger

mdmackillop
03-25-2006, 02:52 AM
Hi Holger,
I'd just noticed and rewritten with the ABS. You beat me to it!

zest1
03-25-2006, 08:23 AM
Thanks to both of you for your great help (and opportunity to learn).

When I run the VBA, it still returns the smaller number (date) rather than the closest larger one.

Perhaps I have missed something. I attached the file for you to see. Maybe a small adjustment in the code will make it work. Can you please check it and let me know what the problem is?

Thanks again!

mdmackillop
03-25-2006, 08:56 AM
I only want the lower value if it is in fact closer than the higher value
Hi Zest,
Holger renamed my function to GetDate2
Either enter =GetDate2(C3,G1:G20)
or (preferred)
Change the all mentions of GetDate2 to GetDate in the function.

zest1
03-25-2006, 09:11 AM
Thanks for that md, I should have caught that.

Unfortunately, after changing 'GetDate2' to 'GetDate', the code still returns the smaler value (date), even though the larger one is closer. Is it working for you? What else would you suggest?

Shazam
03-25-2006, 09:22 AM
Is this what you are looking for?


In cell B2:

=LARGE(Table!A1:A8,COUNTIF(Table!A1:A8,">"&A2)+1)



In cell D2:

=SMALL(Table!A1:A8,COUNTIF(Table!A1:A8,"<"&A2)+1)

mdmackillop
03-25-2006, 09:36 AM
Try the attached

Shazam
03-25-2006, 09:36 AM
After re-reading your post. Does this work? It will grab whatever date is closet to the criteria date.


=INDEX(Table!A1:A8,MATCH(MIN(ABS(Table!A1:A8-A1)),ABS(Table!A1:A8-A1),0))


Must be confirmed with:


CTRL+SHIFT+ENTER

zest1
03-25-2006, 10:19 AM
thanks MD and Shazam!

Shazam, thanks a lot, that's what I was looking for. Simply a 'rounding' to the next higher closest value. MD, your formulas are still returning the lower looked-up values, rather than jumping to the next higher one.

Thanks a lot to you all, I really appreciate your help on this. :) :)

mdmackillop
03-25-2006, 10:22 AM
Hi Zest,
In Post 6 you specifically asked for the lower one if it was closer.

Both methods return the same results.

zest1
03-25-2006, 10:56 AM
my apologies mdmackillop!

So sorry. Yes, your code also does exactly what I wanted. Somehow I misread the dates it was returning.

BTW, some records have the same dates, in which case I'd like them all returned, would that be possible? And which code would you recommend I use (preferably the quicker one)?

Again, I really appreciate your help :)

mdmackillop
03-25-2006, 11:22 AM
Hi Zest,
Can you post a sanitised copy of your workbook to see what you're trying to do.
regards
MD

zest1
03-25-2006, 01:25 PM
Hi MD,

The code you provided me is perfect for finding the closest (or exact) single value. But when there are several common values (dates) matching, it only returns one of them and ignores the others. I attached a slightly modified version of your last file to show what I mean.

Would I need to use a criteria range (ie. + or - number of days ahead or behind the search date) rather than an exact date in order to have the other records returned? Or, should I just use the month & year instead of exact date [ie. DATE(YEAR(MONTH(...)) ]? Advanced Filter won't work here since my data is in a separate workbook.

Thanks a lot for your kind help.

mdmackillop
03-25-2006, 02:02 PM
I think your needing a macro do do this. Here's a userform method

BTW, How about adding your flags to your profile. Always intersting to see where our members are from.

lucas
03-25-2006, 02:09 PM
Thats pretty darn slick Malcolm

lucas
03-25-2006, 02:18 PM
I did change the textbox1 format to mm/dd/yy

Shazam
03-25-2006, 02:26 PM
Hopefuly I did this right.

Input this formula in cell D3 then copy it to in cell E3 and fill it down.


=IF(ROW()>ROW(D$2)+COUNTIF($G:$G,$D$1),"",INDEX(H:H,MATCH($D$1,$G:$G,0)+ROW()-ROW(D$3)))


Look at the sample attahcment below.

zest1
03-25-2006, 02:55 PM
Thank You!

Shazam, that is totally awesome. Very cool, and so efficient. And works like a charm.

MD, thank you too. I'd later like to incorporate the -days/+days (or months) idea for another situation, so I may be back.

You guys are a great help, and I really appreciate it.
Thanks again :) :)

HaHoBe
03-26-2006, 01:50 AM
Hi, Shazam,

thanks for showing how to solve the problem with array formulas - I think I should be spending more time on learning about them.

Ciao,
Holger