# Excel formula help - index match or maybe an array

I am having difficulty figuring out the formula needed to return a time value that is between 6 and 15 mins higher than the look up value.

For example.
1. I have a train arriving at 5:12
2. I need to look at a range of times to find the next closest time to 5:12 (between 6 and 15 mins)
3. In this example that time would be 5:17. I then need to add in the train for wynyard that matches the 5:17 st marys train.
4. In this example it would be 6:18

I have attached a file that hopefully explains it a bit better than above. I have tried an index match formula based on matching St Marys with St Marys, however I  can't work out how to get the next available time based on my rule of 6 and 15 mins.

Any help on this will be greatly appreciated!
Example-sheet.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
How is 5.17 between 6 and 15 minutes from 5.12?
Author Commented:
sorry my fault it should have been 5:18
Author Commented:
don't laugh at the basic formula haha but I can get it to work IF I specify how many minutes (cell F1) however I would like to just search a range of data and return a number that will be between 6 and 15 not just 6 mins.

Am I approaching this all wrong and should consider a different formula all together?
Example-sheetv1.xlsx
Commented:
Microsoft Excel ExpertCommented:
@user2073   i agree with you, it is not an easy formula. it took me couple of minutes to come up with this solution.

please find attached workbook with working formula.

or you could put this single formula in B4 and then copy horizontally and vertically

``````=INDEX(INDEX(\$B\$22:\$I\$26,MATCH(\$A4,\$A\$22:\$A\$26,0),0),MATCH(1,INDEX((INDEX(\$B\$22:\$I\$26,MATCH(\$A4,\$A\$22:\$A\$26,0),0)>=B\$3)*(INDEX(\$B\$22:\$I\$26,MATCH(\$A4,\$A\$22:\$A\$26,0),0)<>""),0),0))
``````
EE.xlsx

Experts Exchange Solution brought to you by