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

 

[FormR]

Hi, welcome to the forum!

What version of Excel are you using? If it's XL365, you could try:

=LET(SB,SORTBY($L$3:$L$11,$P$3:$P$11),INDEX(FILTER(SB,SB<>$M$1),L13))

 

[harshilrks]

Thank you that's amazing and is working

Can you explain how this formula is working?

Also is there a way to to filter out two cells, say M1 as well as N1 too?

Hi, welcome to the forum!

What version of Excel are you using? If it's XL365, you could try:

=LET(SB,SORTBY($L$3:$L$11,$P$3:$P$11),INDEX(FILTER(SB,SB<>$M$1),L13))

 

[Fluff]

Thank you that's amazing and is working

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[FormR]

Can you explain how this formula is working?

Hi, it uses the SORTBY() function to sort the list of names in column L by the numbers in column P, it then FITERS() the sorted list of names to exclude any that equal what is in cell M1 and then it uses the INDEX() function to retrieve the row number of the filtered list that is in cell L13.

Also is there a way to to filter out two cells, say M1 as well as N1 too?

You could try:

=LET(SB,SORTBY($L$3:$L$11,$P$3:$P$11),INDEX(FILTER(SB,(SB<>$M$1)*(SB<>$N$1)),L13))

 

[harshilrks]

Hi, it uses the SORTBY() function to sort the list of names in column L by the numbers in column P, it then FITERS() the sorted list of names to exclude any that equal what is in cell M1 and then it uses the INDEX() function to retrieve the row number of the filtered list that is in cell L13.



You could try:

=LET(SB,SORTBY($L$3:$L$11,$P$3:$P$11),INDEX(FILTER(SB,(SB<>$M$1)*(SB<>$N$1)),L13))

Thank you so much for your help. Your help has been faboluous

 

[harshilrks]

Hi, it uses the SORTBY() function to sort the list of names in column L by the numbers in column P, it then FITERS() the sorted list of names to exclude any that equal what is in cell M1 and then it uses the INDEX() function to retrieve the row number of the filtered list that is in cell L13.



You could try:

=LET(SB,SORTBY($L$3:$L$11,$P$3:$P$11),INDEX(FILTER(SB,(SB<>$M$1)*(SB<>$N$1)),L13))

Sorry one more build if you are able to help

Instead of filtering out values if its not a value with INDEX(FILTER(SB,(SB<>$M$1) can we filter using a IF condition where if the value in column B is a specific value, then we filter out value of column A

For example:

Column A	Column B
Test	Off
Test2	Off
Test 3

Filter out column a value if column b is "off"

Thanks!

 

[FormR]

Hi, something like this maybe.

Book2
	A	B	C								K	L	M	N	O	P
1
2	Name 1	OFF
3	Name 2											Name 1				1
4	Name 3											Name 2				12
5	Name 4	OFF										Name 3				13
6	Name 5											Name 4				2
7	Name 6	OFF										Name 5				4
8	Name 7											Name 6				3
9	Name 8											Name 7				12
10												Name 8				20
11
12
13												1	Name 5
Summary

Cell Formulas
Range	Formula
M13	M13	=LET(SB,SORTBY($L$3:$L$11,$P$3:$P$11),INDEX(FILTER(SB,ISNA(MATCH(SB,FILTER($A$2:$A$9,$B$2:$B$9="OFF"),0))),L13))

 

[harshilrks]

Your a real wizard! That works when searching one column e.g "Column B", if I want to extend the same logic but to multiple columns e.g if Column C also contains "Off" , would I just copy the same formula but change to values for column C?

 

[FormR]

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?