need to pull a max value based on highest quarter of a date range

[cblouin]

Hello Everyone!!

I attached a workbook with some sample data and basic formulas. I'm looking for the best formula to put in cell B6.

I'm not very proficient with the different date functions and need some help with this problem. Based on 2 date inputs, start date and end date, I need to reference a data table with quarterly interest rates. Out of all of the quarters that are touched by the date inputs, I need to pull the interest rate that is the highest.

For example, if the start date is 1/1/17 and the end date is 12/31/17, then I need to reference the rates for the 4 quarters in 2017 and pull the highest rate.

In the workbook example, the start date is 1/15/17 and the end date is 4/5/19. I need to compare all 4 quarters of 2017, all 4 quarters of 2018, and the first 2 quarters of 2019 and use the highest rate out of those 10. In the example, the highest quarter is the 2nd quarter of 2017 with a rate of 0.000034.

Any help is much appreciated!!

[FlameRetired]

If I understand correctly try this formula in B6.

Please Login or Register  to view this content.

[cblouin]

Thank you so much for the reply, Dave! That formula does work to give me the outcome for that test, but I noticed an issue when I manipulated some of the rates in column I. For the sample worksheet, using 1/15/17 and 4/5/19 as the start and end dates, the formula you provided works for any of the highest rates in I6:I13. I actually needed it to also include I5 and I14 for that example. If I change either of those 2 fields in I5 or I14 to be the higher rate, then the formula won't pull that number. I'll try to mess around with it today to see if I can get it to include those 2 fields for this test.

[cblouin]

B3 needs to be >= G3:G14 and B4 needs to be <= H3:H14. I just have to figure out how to get that in there...don't know how to flip that around

[cblouin]

I messed around with the workbook today and found a solution. I had to add a few extra columns for some IF formulas, but it appears to give me the result I was looking for. There's probably a more simplified way to do it, but this seems to work. Have a great day everyone! Be safe.