Nested IF ISNA MATCH

[limo coney]

Hi everyone,

I've been breaking my head with this one. could you please help me with this formula?
=IF(ISNA(MATCH(BH13,T13:AT13,0)),BH13,IF(ISNA(MATCH(BH13,T13:AT13,0)),M13,IF(ISNA(MATCH(BH13,T13:AT13,0)),N13,"")))

I'm trying to make a roster where formula automatically chooses the next person available if someone's on leave (T13:AT13)

 

[Sufiyan97]

Hi & welcome to MrExcel.

Can you post some rows of your data using below link for xl2bb

 

[limo coney]

Thanks Sufiyan,

Essentially what i'm looking for is, there needs to be someone present at all times from column F to O, but one person can be there at more than one column. For example if F2 is on AL then J2 needs to cover both, but if F2 and J2 both are off then N2 needs to cover all three and so on. Column S to AE are separate dept so when they are there ( i haven't populated that yet but essentially R means staff required where as N or empty means not required) they can't be in column F to O and Column AF onwards is leave.

I want to write a formula for column F to O where it'll automatically allocate people if they are on leave or in another department.

Thanks

Arun

 

[limo coney]

Thanks Sufiyan,

Essentially what i'm looking for is, there needs to be someone present at all times from column F to O, but one person can be there at more than one column. For example if F2 is on AL then J2 needs to cover both, but if F2 and J2 both are off then N2 needs to cover all three and so on. Column S to AE are separate dept so when they are there ( i haven't populated that yet but essentially R means staff required where as N or empty means not required) they can't be in column F to O and Column AF onwards is leave.

I want to write a formula for column F to O where it'll automatically allocate people if they are on leave or in another department.

Thanks

Arun
View attachment 83044

MREXCEL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			On call		Staffing number	Ca	Br	2nd Br	3rd Br	Tx	H	P	IR	Re	2nd Re	Bx	Admin		L1	M1	N2	C2	M2	L3	M3	A3	S3	C5	J3	SE	P3	Pre/post on call off	Annual Leave (1)	Annual Leave (2)	Annual Leave (3)	Study Leave/OTHER	Study Leave/OTHER	Study Leave/OTHER
2	Wednesday	01/02/2023	5pm-9am	am		J	S	K	B	S	S	B	K3	N	K2									R	R
3			A	pm		J	S	K	B	S	S	B	K4	N	K3											N	R
4	Thursday	02/02/2023	5pm-9am	am																								R				S
5			B	pm																									N			S
6	Friday	03/02/2023	5pm-9am	am
7			S	pm
8	Saturday	04/02/2023	9am-9am	am
9			H	pm
10	Sunday	05/02/2023	9am-9am	am
11			S	pm
12	Monday	06/02/2023	5pm-9am	am		J	S	K	B	S	S	B	K3	N	K2																	H	B
13			N	pm		J	S	K	B	S	S	B	K4	N	K3				R	N												H	B
14	Tuesday	07/02/2023	5pm-9am	am																	R											S
15			K	pm																		R	N									S
16	Wednesday	08/02/2023	5pm-9am	am																				N	N								H			K
17			K2	pm																						N	N						H			K
18	Thursday	09/02/2023	5pm-9am	am																								R				B				K
19			H	pm																									R			B				K
20	Friday	10/02/2023	5pm-9am	am																													K2			K
21			B	pm																													K2			K
22	Saturday	11/02/2023	9am-9am	am
23			K3	pm
24	Sunday	12/02/2023	9am-9am	am
25			B	pm
Sheet4