Index and match formula that ignores dynamic value

[harshilrks]

Hi all

I have the following sheet and I'm trying to use an index and match fomrula but it ignores a dynamic value. In colum O13, I'm using the index and match to return the person with the lowest value.

This works as intended but I need a way to continue this but to ignore the name/person from cell M1. How can I do this?

Lead analyst triage.xlsm
	A		C		E		G		I	J	K	L	M	N	O	P	Q		S		U
1												Lead analyst	DJ
2	Type		Priority		Bounce back?		Review		Assignee	Score		Analyst Health	Days working	Weighting for days off	Current score	Total score	Open		Pending		On Hold
3	SIEM		Low		No		No		Harshil	3		Harshil	5	1	0	6	0		0		0
4	SIEM		Low		No		No		Harshil	3		Andres	5	1	0.5	3.5	0		2		0
5	Sophos		High		No		Yes		Grad	3		Yulia	5	1	0.25	2.25	0		1		0
6	MDE		High		No		Yes		Shreyash	4		DJ	5	1	2000	2000	0		0		0
7	SIEM		High		No		Yes		Yulia	2		Arun	5	1	0.75	1.75	0		3		0
8	SIEM		Normal		No		Yes		Andres	1		Shreyash	5	1	0	4	0		0		0
9	SIEM		Normal		No		Yes		Arun	1		Craig	5	1	1.25	2.25	1		3		0
10	SIEM		Normal		No		Yes		Craig	1		Gradrgrgrgr	5	1	0	8	0		0		0
11	NCC		Normal		No		No		Andres	2		Matt	3	0.6	1999	1999	0		2		0
12	NCC		High		No		Yes		Arun	0		Next person to assign too			Analyst
13	NCC		High		No		Yes		Arun	0		1			Arun
Lead allocation

Cell Formulas
Range	Formula
O3:O5,O7:O10	O3	=SUM($R3+$T3+$V3)
N3:N11	N3	=IF($M3=5,"1",IF($M3=4,"0.8",IF($M3=3,"0.6",IF($M3=2,"0.4",IF($M3=1,"0.2",IF($M3=0,"0"))))))
P3	P3	=(SUMIF($I$3:$I$459,"Harshil",$J$3:$J$459)*$N3)+$O3
P4	P4	=(SUMIF($I$3:$I$459,"Andres",$J$3:$J$459)*$N4)+$O4
P5	P5	=(SUMIF($I$3:$I$459,"Yulia",$J$3:$J$459)*$N5)+$O5
P6	P6	=(SUMIF($I$3:$I$459,"DJ",$J$3:$J$459)*$N6)+$O6
P7	P7	=(SUMIF($I$3:$I$459,"Arun",$J$3:$J$459)*$N7)+$O7
P8	P8	=(SUMIF($I$3:$I$459,"Shreyash",$J$3:$J$459)*$N8)+$O8
P9	P9	=(SUMIF($I$3:$I$459,"Craig",$J$3:$J$459)*$N9)+$O9
P10	P10	=(SUMIF($I$3:$I$459,"Grad",$J$3:$J$459)*$N10)+$O10
P11	P11	=(SUMIF($I$3:$I$459,"Matt",$J$3:$J$459)*$N11)+$O11
J3:J13	J3	=SUM($B3+$D3+$F3+$H3)
O13	O13	=INDEX($L$3:$L$11,MATCH(SMALL($P$3:$P$11,$L13),$P$3:$P$11,0))

Cells with Data Validation
Cell	Allow	Criteria
I3:I13	List	=$L$3:$L$11
M1	List	=$L$3:$L$11
C3:C13	List	=Values!$B$3:$B$6
A3:A13	List	=Values!$A$3:$A$7
E3:E13	List	=Values!$C$3:$C$4
G3:G13	List	=Values!$D$3:$D$4

 

[harshilrks]

So exclude the names in column A where the same row in column B or column C is "OFF"? Or exclude the names in column A where the same row in column B and C is "OFF"? Or something else?

Any other variants that need to be considered?

Thanks let me explain a bit further the requirements apologies.

• If Today is Monday and Worker 1 has "Off" it would ignore this and show worker 2 as the lowest score.
• For all other workers where its not got a "Off" it would show the person with the next lowest score.
• If now it is Tuesday it will INCLUDE worker 1 in the calculation as normal but this time EXCLUDE worker 2 as today's date is now Tuesday and he's marked off as "Off" on Tuesday.

I.e if based on today's date, it will check the schedule for that day and for anyone who is marked with "Off" or "Lead" it will exclude them for that calculation for the day. The next day when the day changes it will then use the schedule for that day.

 

[FormR]

Hi, using the layout in your image in post#11 and hardcoding the "rank to return" (the last 1 in the formula) - you can try like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1		Monday	Tuesday	Wednesday	Thursday	Friday
2	Worker 1	OFF
3	Worker 2							Worker 1	2		Worker 1
4	Worker 3							Worker 2	3
5	Worker 4	OFF						Worker 3	5
6								Worker 4	7
Sheet1

Cell Formulas
Range	Formula
K3	K3	=LET(SB,SORTBY($H$3:$H$6,$I$3:$I$6),INDEX(FILTER(SB,1-ISNUMBER(MATCH(SB,FILTER($A$2:$A$5,INDEX($B$2:$F$5,0,MATCH(TEXT(TODAY(),"dddd"),$B$1:$F$1,0))="OFF"),0))),1))

 

[FormR]

for anyone who is marked with "Off" or "Lead" it will exclude

I missed the "Lead" part - if those cells can only contain "Off" or "Lead" then you can try like this, if they can contain other text that you don't want to exclude post back and we'll have another go.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1		Monday	Tuesday	Wednesday	Thursday	Friday
2	Worker 1	OFF
3	Worker 2		OFF					Worker 1	2		Worker 1
4	Worker 3							Worker 2	3
5	Worker 4	OFF						Worker 3	5
6								Worker 4	7
Sheet1

Cell Formulas
Range	Formula
K3	K3	=LET(SB,SORTBY($H$3:$H$6,$I$3:$I$6),INDEX(FILTER(SB,1-ISNUMBER(MATCH(SB,FILTER($A$2:$A$5,INDEX($B$2:$F$5,0,MATCH(TEXT(TODAY(),"dddd"),$B$1:$F$1,0))<>""),0))),1))

 

[harshilrks]

I honestly can't thank you enough, you really are a wizard

Although the complexity of that formula is frightening thanks for your time its really appreciated- is this just through pure experience you are able to create these so seamlessly

 

[FormR]

Glad you've got it working

is this just through pure experience

Mainly it's the experience gained from contributing towards, and more importantly, learning from the contributions of others, to posts on this board.