0

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.

enter image description here

Sammy
  • 669
  • 2
  • 8
  • 13

2 Answers2

1

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.

        enter image description here

Community
  • 1
  • 1
  • This shows that `AGGERATE` is **not** the solution avoiding array formulas at all. Because `=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($1:$1048576)/(($A:$A<$F4)*($B:$B>$F4)*($D:$D=G$3)),1)),"")` is as much time consuming as an array formula would be. – Axel Richter Feb 28 '16 at 09:35
  • 1
    Axel, AGGREGATE processes the same as SUMPRODUCT; it does not truncate at the used range like a SUMIFS, etc. fwiw, I never said it did and cut the processing range down to 7 rows in this solution. If fact, a nested IF based array formula would be slightly faster since latter IF conditions are not processed at all if the first or second fails. –  Feb 28 '16 at 09:39
  • Yea I'm working with lots of data so speed is an issue. Might try the nested if suggestion. – Sammy Feb 28 '16 at 10:23
  • 1
    @Sammy - If you have sufficient data that it makes a formula or array formula insufficient then you should be looking at a VBA solution where all processing is in-memory and dumped back to the grey data table *en masse*. –  Feb 28 '16 at 10:33
0

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.

Sammy
  • 669
  • 2
  • 8
  • 13