Get dollar value from adjacent cell where day of month is in a date range-CodePudding

I have an excel workbook with two worksheets.

One holds static values that lists bill due each month, with a day of month column as an integer (or it can be, right now its attached to a string), a bill name column as a string and a dollar amount. I would like to pull the bill name and the amount if the day of month falls within a date range on the other sheet.

The other sheet lists out a date range of a week for the whole month, and includes the name of the bill, and the amount due during that week. I would like to automatically populate those columns using values from my static sheet, where the day of month in the static sheet falls between the date range in the month sheet. I have attached screen shots of what I am aiming for. This is simply sample data. I would like the month sheet columns(Both bill name and amount) to be auto populated when I enter dates in the header columns from the static sheet.

Assume the static values sheet is named "Static", and the month sheet is named "December".

I would be happy to provide more information/images/sample files as needed. This is a hobby of mine, and questions, discussion, criticism is encouraged.

Update

With the formula posted by mark, this works flawlessly when the date ranges are within the same month.

=LET( st, H1,  fn, I1,
      expenses, $D$3:$F$10,
       na, INDEX( expenses, SEQUENCE( ROWS( expenses ) ), {1,2}), d, INDEX( expenses,, 3),
       FILTER(na, (d>=DAY(st))*(d<=DAY(fn)) ) )

However, when the date ranges include a month change for example: December 30 through January 5th, Excel shows an error "Empty arrays are not supported".

CodePudding user response：

With Office 365, you can do:

=LET( st, H1,  fn, I1,
      expenses, $D$3:$F$10,
       na, INDEX( expenses, SEQUENCE( ROWS( expenses ) ), {1,2}), d, INDEX( expenses,, 3),
       FILTER(na, (d>=DAY(st))*(d<=DAY(fn)) ) )

where H1 is your starting date of the period, I1 is the finishing date and D3:F10 is the array of Name, Amount and Day of month as you have shown.

This formula will spill the results with Name and Amount. It can be copied to other cells as you have shown in your Sheet2 example (i.e., A3, D3, G3... in Sheet2).

If you don't have Office 365, it will be harder.

Version II - Account for month cross over

This will accommodate the crossover of the month. Did not think about that...

=LET( st, H1,  fn, I1,
      expenses, $D$3:$F$10,
       s, DAY( st ), f, DAY( fn ),
       na, INDEX( expenses, SEQUENCE( ROWS( expenses ) ), {1,2}), d, INDEX( expenses,, 3),
       b, IF( MONTH(st) = MONTH(fn), (d>=DAY(st))*(d<=DAY(fn)), (d>=DAY(st))*(d<=DAY(EOMONTH(st,0)))   (d>=1)*(d<=DAY(fn)) ),
       FILTER(na, b ) )