Formula needed to search a date range and return the fiscal week & fiscal year

[amys39]

Hi!

I have a list of event dates that need to be assigned a Fiscal Year and Fiscal Week. Here's an example:

Event date = 11/18/99

Search the following Week Ending Dates list and return the Fiscal Year & Fiscal Week so the return value would be: FY00 W08

A B
Week Ending Date Fiscal Week
10/2/99 FY00 W01
10/9/99 FY00 W02
10/16/99 FY00 W03
10/23/99 FY00 W04
10/30/99 FY00 W05
11/6/99 FY00 W06
11/13/99 FY00 W07
11/20/99 FY00 W08
11/27/99 FY00 W09
12/4/99 FY00 W10
12/11/99 FY00 W11
12/18/99 FY00 W12
12/25/99 FY00 W13
1/1/00 FY00 W14
1/8/00 FY00 W15
1/15/00 FY00 W16
1/22/00 FY00 W17
1/29/00 FY00 W18
2/5/00 FY00 W19
2/12/00 FY00 W20
2/19/00 FY00 W21
2/26/00 FY00 W22
3/4/00 FY00 W23
3/11/00 FY00 W24
3/18/00 FY00 W25
3/25/00 FY00 W26

Where A2 begin the Week Ending date and B2 begins the Fiscal Week date

 

[jasonb75]

Maybe =INDEX($B$2:$B$20,match(C2,$A$2:$A$20))

Where C2 holds the date to look up, expand column ranges to fit your data.

 

[Aladin Akyurek]

Looks like...

=INDEX($B$2:$B$27, MATCH(E2, $A$2:$A$27, 1) + (LOOKUP(E2, $A$2:$A$27) < A2))

 

[amys39]

The problem is that the event dates can fall between the week ending dates so if for example the event date is 10/7/99 then the return value would need to FY00 W02...does that make sense

 

[amys39]

Aladin Akyurek: this looks like it's very close but for some reason when I test it with date 10/15/1999 it's returning FY00 W02 when it should return FY00 W)3....

 

[jasonb75]

How about

=INDEX($B$2:$B$27,MATCH(C2,$A$2:$A$27,1)+ISNA(MATCH(C2,$A$2:$A$27,0)))

Just realised my error too late to edit.

 

[amys39]

YES! That's perfect! Thank you

 

[jasonb75]

Aladin, could you explain your thought behind + (LOOKUP(E2, $A$2:$A$27) < A2) please, I'm guessing it was an error correction factor, but can't see how it's meant to work.

 

[Aladin Akyurek]

Aladin Akyurek: this looks like it's very close but for some reason when I test it with date 10/15/1999 it's returning FY00 W02 when it should return FY00 W)3....

There is a typo in my suggestion...

=INDEX($B$2:$B$27, MATCH(E2, $A$2:$A$27, 1) + (LOOKUP(E2, $A$2:$A$27) < E2))

By the way, this formula is way faster...

 

[jasonb75]

Aladin,

In your opinion, would =VLOOKUP(CEILING(C2,7),$A$2:$B$27,2,0) be faster than the other suggestions? One observation, there are 6 days before the first week ending date that would give #N/A using the original suggestions, which this would overcome.

The exact match could be omitted, but including it would reject any dates that fall outside of the lookup table scope.