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

 

[Aladin Akyurek]

Aladin,

In your opinion, would =VLOOKUP(CEILING(C2,7),$A$2:$B$27,2,0) be faster than the other suggestions?

With match-type = 1, we have binary search algorithm which is very fast.

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

Two options. [1] Extend the table with 0 or even -9.99999999999999E+307. [2] Expand the formula with IF(LookupValue>=MatchRange'sFirstValue,...), still preserving performance to a large degree.