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:
[vba]{=INDEX(Table!A1:A100,MATCH(MIN(IF(Table!A1:A100>=A1,Table!A1:A100)),Table! A1:A100,0))}[/vba]
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

[vba]
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

[/vba]

**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

**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!

**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)

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

**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

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

Thread: Lookup for Next (not last) Date

Thread Tools

Display

Lookup for Next (not last) Date

Posting Permissions