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.

spreadsheet image

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

Thanks Oli

Answers


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?


Need Your Help

jQuery hoverIntent Not Firing if mouse is already over the element on document ready

jquery hoverintent

I am using the jQuery plug-in hoverIntent. The problem is sometimes when the page is loaded, the mouse is already over the object '#gallery', and will therefore not trigger the hoverIntent code.

Android: Require a image to be saved temporarily

android image caching storage

I have a scenario which I am not sure which type of storage to use. I have a viewpager where each pages required to save a bitmap in local device which user have drawn on the page. If I used applic...