Create Fiscal Year - Lookup Date in Fiscal Month Date Range and Return EOM Date

My company recently switched from using the calendar months as our fiscal months to using the an altered fiscal month calendar. I.e. - the fiscal month of January 2013 began on 12/26/2012 and ends on 1/25/2013.

I'm looking for a function that will search search a date array, find where the date falls in the array and return a specific end of month date.

Please see table below -
Column A = fiscal month start date
Column B = fiscal month end date
Column C = calendar EOM date

I need a function that would look up the date in Column E - "Date to Look up," determine where the date falls in fiscal calendar and return the corresponding row in Column C - "EOM Date to Return" as the result.

Any help is much appreciated!

Month Start	Month End	EOM Date to Return		Date to Lookup	Result
12/26/2012	1/25/2013	1/31/2013		12/24/2012	n/a
1/26/2013	2/25/2013	2/28/2013		12/25/2012	n/a
2/26/2013	3/25/2013	3/31/2013		12/26/2012	1/31/2013
3/26/2013	4/25/2013	4/30/2013		1/24/2013	1/31/2013
4/26/2013	5/25/2013	5/31/2013		1/25/2013	1/31/2013
5/26/2013	6/25/2013	6/30/2013		1/26/2013	2/28/2013
6/26/2013	7/25/2013	7/31/2013		3/26/2013	3/31/2013
7/26/2013	8/25/2013	8/31/2013
8/26/2013	9/25/2013	9/30/2013
9/26/2013	10/25/2013	10/31/2013
10/26/2013	11/25/2013	11/30/2013
11/26/2013	12/25/2013	12/31/2013

Hi,

You can use the vlookup function with the range_lookup argument set as true (or omitted)
Here is how you will use it:

Formula:

Please Login or Register  to view this content.

-DateToLookUP should refer to the date you want to lookup
-LookUpRange should select the following 2 columns: the month end column and the EOM column.

Cheers

@Mayda89 - Thanks for the help I got it to work. One correction that I had to make for it to work.

Assuming Month Start = Column A | Month End = Column B | EOM Date to Return = Column C | Date to lookup = Column D - the table_array had to contain A3:C14 - i.e. the entire table including start date, end date, and date to return. I then set Col_index_num to 3 and Lookup_value to D3 and the function returned the correct value.

Thanks again for your help.

Glad that I could help!