Google Spreadsheet calendar formula

Question

I'm trying to make a Google Spreadsheet that can create a sort of calendar for my volunteer org. The aim is to input the information on the left side [ID No., Name, Start Date, and End Date], and, using the dates inputted, automatically generate a calendar and set them into slots based on the inputted dates. I'm assuming they're signing up for every day on and between the dates.

This is a screenshot of the Excel sheet:

I'm having a little trouble with the formula for creating the automatic slots on the right hand side, as an experienced user can probably tell. I hope to make this as easy as possible for the user (so they don't mess it up)

The formula for G3 (highlighted in the image):

=INDEX($B$3:$B$24,SMALL(IF(G$2>=$C$3:$C$24,ROW($C$3:$C$24)-2),$F3))

The formula should be comparing the date above the column with the dates provided (which most likely won't be completely sorted, either). I used INDEX because VLOOKUP only calls the first result every time, and I couple the INDEX with the SMALL so I could keep the order the same as well.

The idea of the formula is to create create the list of volunteers for each day for each of the day's they're available. I've figured out how to generate slots for after the days after they're available, but I'm having trouble figuring out how to make it so it take them out on the dates they're no longer available ( as you can see on my example with the sample numbers, it just adds them and there's no way to take them back).

I can't think of way to use IFS or AND-IFS functions to call upon that last row number.

Answer 1

Why not use a query formula

=QUERY(
  $A$3:$D$12, 
  "SELECT B 
   WHERE 
     DATE '"& TEXT(F2, "YYYY-mm-dd") & "' >= C AND
     DATE '"& TEXT(F2, "YYYY-mm-dd") & "' <= D", 0)