Formula to evaluate a date between ranges and return a value in an array

Copper Contributor

I am trying to create a spreadsheet that has a Master Page that I can flip between Months. I've got my Drop down list figured out and I'm close (I think) on the formula, but I need some asssitance. I've attached a couple of screen shots to help.


Sheet 1.jpgIn the Cell C9 I want the value that is found in an array on another Sheet in the workbook. The formula I've written is checking to see if the date on my other worksheet is =<> the dates listed in the screen shot to the left C3:C4. This would change dynamically as I change the month at the top.

I've gotten it to work for January, but it is returning all the dates being searched in the array, and it doesn't work in any of the other months chosen, Just January.


Sheet 2.jpgHere is the Data on the other sheet. I would like it to only return the value in Colum B that corresponds to the month chosen on the main sheet.


Here is the formula I've used so far and the results I've gotten... Can someone please assist in what I'm missing or what needs to be changed to gain the desired result.


=IF(AND('Bills Amount & Due Date'!B3:B15>=C3,L8<=C4),'Bills Amount & Due Date'!B3:B15,"FALSE")

1 Reply


=INDEX('Bills Amount & Due Date'!$B$3:$B$15,MATCH(1,(C3<='Bills Amount & Due Date'!$B$3:$B$15)*(Tabelle10!C4>='Bills Amount & Due Date'!$B$3:$B$15),0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.


Bills Amount & Due Date:

Bills Amount & Due Date.JPG


Other Sheet:

other sheet.JPG