Date Buckets - reversing order on vlookup?

[happydays886]

Hey all,

I am trying to create buckets for date information using formulas. The objective is to put the correct WE date beside the actual date.

I went with Vlookup @ Range = "true", but the information is grouping in reverse to what would make this perfect.

Help is appreciated.

Cheers,

[happydays886]

So, I did figure this out using a completely different approach. If anyone is interested in exactly what I was trying to do,
- 365 days in a year, 5 years data => bucket these by a uniform week-ending date and month for a pivot extract.

This is accomplished through:=date(year(f3),month(f3),day(f3)+choose(weekday(f3),6,5,4,3,2,1,0,-1))

The initial logic was to reverse the way a "true" range in the vlookup organizes information.

[shg]

Or

=F3 + 7 - WEEKDAY(F3)

I don't know what you mean for the rest of the question.

[happydays886]

Jesus. That is one efficient formula! Wow.

I'll explain better.

Using the formla =F3-WEEKDAY(F3), you end up with the same result when applying the vlookup(F3,___,2,TRUE) in the sense that they both 'count backwards' ("TRUE" in vlookup does the reverse count).

F3+7-weekday(F3) counts forward 7.

I hoping you could reverse what happens when you have "TRUE" in the vlookup, to essentially give it that forward looking output (like the "+7" in the formula you wrote).

[Pete_UK]

You can use this formula in G3:

=IF($F3<=$B$4,$C$4,INDEX(C$4:C$8,MATCH($F3,$B$4:$B$8,TRUE)+IF(ISNUMBER(MATCH($F3,$B$4:$B$8,FALSE)),0,1)))

Format as a date, then copy down. Then use this in H3:

=IF($F3<=$B$4,$D$4,INDEX(D$4:D$8,MATCH($F3,$B$4:$B$8,TRUE)+IF(ISNUMBER(MATCH($F3,$B$4:$B$8,FALSE)),0,1)))

(changes shown in red). You don't need to format this as a date, as your entries in column D are text values.

Hope this helps.

Pete

[shg]

I hoping you could reverse what happens when you have "TRUE" in the vlookup, to essentially give it that forward looking output (like the "+7" in the formula you wrote).

Does Pete's formula do what you want?

If not, please describe the result you're trying to achieve, and give an example.

[Pete_UK]

The example is shown in the green cells in the sample file, shg. The OP wants to obtain the next row's data from where the match occurs (unless it is an exact match), so that's why I changed it to INDEX/MATCH.

(I think)

Pete

[Teethless mama]

G3: =VLOOKUP(MIN(IF($B$4:$B$8>=$F3,$B$4:$B$8)),$B$4:$D$8,1+COLUMNS($G3:G3),0)

Press Ctrl+Shift+Enter, not just Enter
Copy down and across