Lookup and calculation with mulitple criteria also based on a cell value
I'm trying to create a spreadsheet that will allow me to quickly calculate the amount of time my trains were delayed on a daily basis.
I need a formula that will check for all trains on a particular route after a planned departure time (written in a cell),check these trains actual arrival time and then display the earliest possible time I could have arrived at my destination.
For example, in G4 I would like a formula that looks for all trains that depart after 7:49 (A4) and also match both of it's "From" and "To" (C4 & D4). It would then need to check these trains corresponding "actual arrival times" in column F and show the earliest possible train. So for row 4 this would be 9:36.
Any help would be really appreciated as I have been messing around with this for over a day and have gotten nowhere!
A link to the example is here - https://docs.google.com/spreadsheets/d/1eE8t4-_hKB6o5j3W57EHgKzsF9p1usm7nojerjmrDwY/edit#gid=0
Not sure about 9:36 Do you mean 9:39 ?
It's a little difficult to do this but i think what you are looking for is a multiconditional lookup array. I have put below what I think you are trying to achieve.
If A2:A8 is greater than A4, C2:C8 = C4 and D2:D8 = D4, what is the lowest value in F2:F8
Is this correct?
If so then I came up with this formula: =ArrayFormula(MIN(IF((A2:A8>A4),IF((C2:C8=C4),IF((D2:D8=D4),F2:F8)))))
If you get 0.402 or something, format the cell to time. Otherwise, could you break it down for us a bit more?