Simplifying a mammoth formula

[JackieG63]

Hello, I'm afraid I'm self taught on Excel, therefore this formula is a monster.

I'm trying to put together a visual rota showing which nurses are on which duties at which times.

The HUGE formula I have put together returns VALUE, therefore I wondered if there is a simpler IF AND OR formula I could use, e.g. To return the result "Admin" is if X is between A and B or C and D or E and F.

Any help would be enormously appreciated.

=IF(AND(Q4>=$B5,Q4<$C5),"Admin"," "),IF(AND(Q4>=$C5,Q4<$D5),"Clinic"," "),IF(AND(Q4>=$D5,Q4<$E5),"Break"," "),IF(AND(Q4>=$E5,Q4<$F5),"Clinic"," "),IF(AND(Q4>=$F5,Q4<$G5),"Admin"," "),IF(AND(Q4>=$G5,Q4<$H5),"Clinic"," "),IF(AND(Q4>=$H5,Q4<$I5),"Break"," "),IF(AND(Q4>=$I5,Q4<$J5),"Clinic"," "),IF(AND(Q4>=$J5,Q4<$K5),"Admin"," ")

Monday

Admin

Clinic

Break

Clinic

Admin

Clinic

Break

Clinic

Admin

Break

Finish

07:30​

07:45​

08:00​

08:15​

08:30​

08:45​

09:00​

09:15​

09:30​

09:45​

Nurse 1

08:30​

08:40​

10:40​

11:00​

12:30​

13:30​

15:00​

15:10​

16:10​

16:30​

#VALUE!

#VALUE!

####

####

####

Nurse 2

08:30​

08:40​

10:40​

11:00​

12:30​

13:30​

15:00​

15:10​

16:10​

16:30​

Nurse 3

08:30​

08:40​

10:40​

11:00​

12:30​

13:30​

15:00​

15:10​

16:10​

16:30​

Nurse 4

08:30​

08:40​

10:40​

11:00​

12:30​

13:30​

15:00​

15:10​

16:10​

16:30​

 

[Macropod]

Perhaps:
=IF(OR(AND(Q4>=$B5,Q4<$C5),AND(Q4>=$F5,Q4<$G5),AND(Q4>=$J5,Q4<$K5)),"Admin",IF(OR(AND(Q4>=$C5,Q4<$D5),AND(Q4>=$E5,Q4<$F5),AND(Q4>=$G5,Q4<$H5),AND(Q4>=$I5,Q4<$J5)),"Clinic",IF(OR(AND(Q4>=$D5,Q4<$E5),AND(Q4>=$H5,Q4<$I5)),"Break","")))

 

[JackieG63]

You are AMAZING - Thank you soooo much

 

[J.Ty.]

Hi,

Welcome to the Board.
Please test =INDEX($A$1:$K$1;MATCH(R1;B$5:K$5;1)).

 

[AlanY]

try this,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4	Monday	Admin	Clinic	Break	Clinic	Admin	Clinic	Break	Clinic	Admin	Break	Finish	7:30	7:45	8:00	8:15	8:30	8:45	9:00	10:45	12:35	14:30	15:30	16:30
5	Nurse 1	8:30	8:40	10:40	11:00	12:30	13:30	15:00	15:10	16:10	16:30						Admin	Clinic	Clinic	Break	Admin	Clinic	Clinic	Break
6	Nurse 2	8:30	8:40	10:40	11:00	12:30	13:30	15:00	15:10	16:10	16:30
7	Nurse 3	8:30	8:40	10:40	11:00	12:30	13:30	15:00	15:10	16:10	16:30
8	Nurse 4	8:30	8:40	10:40	11:00	12:30	13:30	15:00	15:10	16:10	16:30
Sheet3

Cell Formulas
Range	Formula
M5:X5	M5	=IFERROR(INDEX($B$4:$K$4,MATCH(M$4,$B5:$K5,1)),"")

 

[mole999]

Have a look at > How to use the Excel LOOKUP function | Exceljet

 

[JackieG63]

Thank you so much everyone.
I am sorted now and so very grateful for your time.
J