How to compare different values either by formula or vba

[plasteredric]

Im trying to compare values between two cells, based on the values of other cells.

The attached workbook explains it better.

The formula i have in the expected results column works so long as the data is in order, however this isn't always the case, and the formula would have to check that the time is the same for both rows being compared and also find the row containing the next Rank in the sequence.

I'm not sure if a formula would do this or if it can be done using some vba code that I can integrate into my existing code.

Thanks

Book1.xlsx

[werafa]

It sounds like you will need a lookup to find your data row, and an offset to find the data to compare.
as you may need more than one criteria to find your data row, I'd write a function that:

1 accepts key identifying criteria as inputs
2 loops through the table until a match is found
3 returns the value of interest