Finding a figure corresponding to a date

[DSNTNOENUF]

Hi folks,

Any here is appreciated.

What I am trying to do.

I am creating a legal document that must express an interest rate that changes annually. I need to reference a date, see where that date falls between two dates and suck out the interest corresponding to it from an array table. If it was specific dates then I would just use an vlookup formula but because it is a non specific date between two others I am at a loss. Please see the example below.

Date :05/14/2016

Rate index Table:

[TABLE="width: 585"]
<tbody>[TR]
[TD]Fiscal Year 2017[/TD]
[TD]07/01/16[/TD]
[TD="align: right"]06/30/17[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]5.62%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2016[/TD]
[TD]07/01/15[/TD]
[TD="align: right"]06/30/16[/TD]
[TD="align: right"]537[/TD]
[TD="align: right"]5.37%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2015[/TD]
[TD]07/01/14[/TD]
[TD="align: right"]06/30/15[/TD]
[TD="align: right"]902[/TD]
[TD="align: right"]5.12%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2014[/TD]
[TD]07/01/13[/TD]
[TD="align: right"]06/30/14[/TD]
[TD="align: right"]1267[/TD]
[TD="align: right"]5.25%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2013[/TD]
[TD]07/01/12[/TD]
[TD="align: right"]06/30/13[/TD]
[TD="align: right"]1632[/TD]
[TD="align: right"]5.25%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2012[/TD]
[TD]07/01/11[/TD]
[TD="align: right"]06/30/12[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]5.25%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2011[/TD]
[TD]07/01/10[/TD]
[TD="align: right"]06/30/11[/TD]
[TD="align: right"]2363[/TD]
[TD="align: right"]5.37%[/TD]
[/TR]
[TR]
[TD]Fiscal Year 2010[/TD]
[TD]07/01/09[/TD]
[TD="align: right"]06/30/10[/TD]
[TD="align: right"]2728[/TD]
[TD="align: right"]5.62%[/TD]
[/TR]
</tbody>[/TABLE]

Because the date falls in Fiscal 2016 I need 5.37% as the cell value.

Any assistance greatly appreciated.

Thanks,
Tyler Benner

 

[Tetra201]

As long as your fiscal years do not overlap, the following wormula would work:

=SUMIFS(E2:E9,B2:B9,"<="&H2,C2:C9,">="&H2)

where E2:E9 is the rate cell range;
B2:B9 is the FY start cell range;
C2:C9 is the FY end cell range;
H2 is the lookup date cell.

 

[DSNTNOENUF]

As long as your fiscal years do not overlap, the following wormula would work:

=SUMIFS(E2:E9,B2:B9,"<="&H2,C2:C9,">="&H2)

where E2:E9 is the rate cell range;
B2:B9 is the FY start cell range;
C2:C9 is the FY end cell range;
H2 is the lookup date cell.

This works fantastic, thank you very much.

 

[Tetra201]

You are welcome.