Index match lookup formula help

[Chris2022]

Hello everyone, I was here recently and was given great assistance by bebo021999 for an index match formula. I have a similar question pertaining to a data table. I have a separate table with truck driver driving hours limitations. What I would like to do is if I insert a class (1,2 or 3), and a rest time, have the formula return the maximum driving time for a particular driver. For example a driver has a class "1" rest facility, and rests for 1 hour and 50 minutes (01:50), the limitation would be 15 hours maximum driving time. It looks humanly impossible, but this driving time includes the rest period, meal and toilet breaks. I have used the following formula =LOOKUP(B2,F4:H4,INDEX(E5:E12,,MATCH(B1,$F$5:$H$12,0))). This only works for class 1, if I select 2 or 3, it returns #N/A. I have also tried several different combinations of INDEX and MATCH with no success. XL2BB doesn't work, so I have attached a photo of my worksheet. Any assistance is much appreciated. Cell B1 is the rest time and B2 is the class. I have posted this question yesterday, but since I replied to my own post, it looks like somebody has answered my post, so I have reposted this question here again.

 

[KRice]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	Rest time	2:05				Max daily FDP with cabin rest
2	Class	2			Max	Min cabin rest (h)
3					Extended	Class
4	Max Ext. FDP	15:00			FDP	1	2	3
5					14:30	1:30	1:30	1:30
6					15:00	1:45	2:00	2:20
7					15:30	2:00	2:20	2:40
8					16:00	2:15	2:40	3:00
9					16:30	2:35	3:00	Not allowed
10					17:00	3:00	3:25	Not allowed
11					17:30	3:25	Not allowed	Not allowed
12					18:00	3:50	Not allowed	Not allowed
Sheet1

Cell Formulas
Range	Formula
B4	B4	=LOOKUP($B$1,INDEX($F$5:$H$12,,MATCH($B$2,$F$4:$H$4,0)),$E$5:$E$12)

 

[Chris2022]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	Rest time	2:05				Max daily FDP with cabin rest
2	Class	2			Max	Min cabin rest (h)
3					Extended	Class
4	Max Ext. FDP	15:00			FDP	1	2	3
5					14:30	1:30	1:30	1:30
6					15:00	1:45	2:00	2:20
7					15:30	2:00	2:20	2:40
8					16:00	2:15	2:40	3:00
9					16:30	2:35	3:00	Not allowed
10					17:00	3:00	3:25	Not allowed
11					17:30	3:25	Not allowed	Not allowed
12					18:00	3:50	Not allowed	Not allowed
Sheet1

Cell Formulas
Range	Formula
B4	B4	=LOOKUP($B$1,INDEX($F$5:$H$12,,MATCH($B$2,$F$4:$H$4,0)),$E$5:$E$12)

Hi KRice, thank you so much! Worked like a charm! I had been searching for days trying to figure the formula out, and tried so many different combinations of lookup, index and match, but couldn't get anything to work. Greatly appreciate you sharing your excel skills with me to resolve my formula issue, and very promptly.