# 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?
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?
0
Author Commented:
sorry my fault it should have been 5:18
0
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
0
Commented:
0
Commented:
@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
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.