Unique Problem - MATCH and INDEX help please!

[sgreenprice]

Good Afternoon,

I have a database which contains Loop ID Points along a tram line, and am trying to find the average travel time difference between two Loop Points. (See Image 1) https://ibb.co/bCrook

I have gotten as far as extracting Tram ID's from the database on a given loop, and on each Tram ID have extracted the tram times where the Loop ID is triggered. (See Image 2) https://ibb.co/iEvsEQ

I have also written a formula that returns only Tram ID's that exists in both lists returned by the filter. (See Image 3) https://ibb.co/ky5sEQ

The final step is extracting the calculated difference in time between the two Loop Points inputted (as the data is anomalous, e.g. the Tram ID or Times are not necessarily mirrored between Loop ID 1 and Loop ID 2. Ideally we would only return time differences under 40 minutes or the closest time to it (so we are making sure it is the same tram being returned).

If anyone has any ideas on how to make it work, I'd appreciate any advice!

 

[istiasztalos]

Hi,

If that's enough to just look at the second table and check for each entry what is the first entry within 40 minutes for the same tram in the first table you can do that with this formula

=IF(B16<>"",IFERROR(INDEX($B1:$J1,,MATCH(1,IF($B1:$J1-B16<1/24/60*40,1,0)*IF($B1:$J1-B16>0,1,0),0))-B16,"not found"),"")

This goes in first line, first entry of the new time difference table,
B16 is the first line, first entry in the second table
$B1:$J1 is the first line in the first table
then drag across and down