Index Match with 3 criteria

Question

I'm having trouble writing and index match formula with 3 criteria. I have something similar to the green table and want to write a formula to get the grey table.

In the green table there is a start and end time for each "event". so in the grey time series, the time would have to lie between those start and end times.

Answer 1

I think I'll use VBA as @Jeeped suggested. But for now I tried a nested if like this in G4:

   =IFERROR(INDEX($C$2:$C$7,MATCH(G$3,IF($F4>$A$2:$A$7,IF($F4<$B$2:$B$7,$D$2:$D$7,0),0),0)),"")

Not sure what is better for speed.

Answer 2

Try this standard formula in G4,

=IFERROR(INDEX($C:$C, AGGREGATE(15, 6, ROW($1:$7)/
   (($A$1:$A$7<$F4)*($B$1:$B$7>$F4)*($D$1:$D$7=G$3)), 1)), "")

Fill right and down.

This is substantially no different from a Two column lookup in table array using INDEX and MATCH.