Extract Excel items in time range to consecutive time

Here’s a simple table with items in a time range. The desired result table has all items mapped to their consecutive time slots. What formula would you suggest accomplishing this task? Thanks!

Original table:

Result table:

Edit:

I just noticed that SUMPRODUCT() worked for most, but Beta is missing the first minute in the result table (8:49 AM). File is here, https://u.pcloud.link/publink/show?code=XZDCONVZ6mQCoqWVEJB0W7AKyLTvNje9z3IX

Any ideas?

Try using <kbd>XMATCH( )</kbd>

• Formula used in cell <kbd>F2</kbd>

=IF(ISNUMBER(XMATCH(1,($E2>=$B$2:$B$6)*($E2<=$C$2:$C$6)*(F$1=$A$2:$A$6))),"Y","")

Formula needs to fill down and fill right, also please try to change the cell references and ranges accordingly as per your suit.

You can also use a <kbd>BOOLEAN LOGIC</kbd> within a <kbd>SUM( )</kbd> function.

• Formula used in cell <kbd>F2</kbd>

=IF(SUM(($E2>=$B$2:$B$6)*($E2<=$C$2:$C$6)*(F$1=$A$2:$A$6)),"Y","")

Since OP is using Excel 2007 hence replace the formula using <kbd>SUM( )</kbd> with <kbd>SUMPRODUCT( )</kbd> although the <kbd>SUM( )</kbd> would work but it would need to hit <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd> while exiting the edit mode, also since you are not using the updated version, you can use <kbd>MATCH( )</kbd> function in place of <kbd>XMATCH( )</kbd> but using <kbd>SUMPRODUCT( )</kbd> will be faster than the rest.

Here is a screenshot where it shows it is not missing the first time 8:49 AM for Beta

Edit 8/10/2023

Updated formula:

=IF(SUMPRODUCT((TIME(HOUR($E2),MINUTE($E2),)>=$B$2:$B$6)*
               (TIME(HOUR($E2),MINUTE($E2),)<=$C$2:$C$6)*
               (F$1=$A$2:$A$6)),"Y","")

Download the workbook from here: Workbook