Return cell value from table having highest value in corresponding column using multiple conditions ignoring eventual duplicates

[Chris78]

Heya ..
I hope someone can take this challenge. I am tryig to return a cell value when having the highest value in a next column using multiple conditions - knowing I can have duplicate values.
I'm looking for a solution without VBA
Let's say I have the table as below.
I want a formula returning the the cell value from column B from the table line having the highest value in column C - but only considering lines where A = GURS and D = 0

What I already tried but is unsuccessful:
=INDEX(B:B,MATCH(LARGE(FILTER(C:C,A:A="GURS"),1),C:C*($H$2=A:A),0)) - but that is missing the second condition column D=0
=MAXIFS(C:C,D:D,0,A:A,"GURS") - which returns the expected value but using that in an index - match fails since all of the columns can have duplicates
Note the combination of A & B is unique, meaning there is only one "Genkis" of type (Column A) GURS, but multiple lines can have same C/D values

 

[Domenic]

Hi Chris,

First, in future, can you please post any sample data as text or use the add-in xl2BB to post data so that we can easily copy and paste it into Excel in trying to come up with a solution. Thanks!

Now for a possible solution. The following formula returns the individual with the largest value in Column C that meets the criteria. In the event that there are two or more individuals that meet the criteria and have the same largest value in Column C, the formula returns them all.

[Example 1]

chris.xlsm
	A	B	C	D	E	F
1	GURS	Genkis	44	15		Results
2	GURS	Rohan	18	0		Rohan
3	GURS	Vanes	42	15
4	GURS	Yasin	43	10
5	GURS	Aaron	15	0
6	VERBS	Fat	32	8
7	VERBS	Etha	7	0
8	VERBS	Chris	43	12
9	VERBS	Ines	10	0
10	VERBS	Patrice	43	8
Sheet1

Cell Formulas
Range	Formula
F2	F2	=LET(maxValue,MAXIFS(C1:C10,A1:A10,"GURS",D1:D10,0),FILTER(B1:B10,(A1:A10="GURS")*(C1:C10=maxValue)*(D1:D10=0)))

[Example 2]

chris.xlsm
	A	B	C	D	E	F
1	GURS	Genkis	44	15		Results
2	GURS	Rohan	18	0		Rohan
3	GURS	Vanes	42	15		Aaron
4	GURS	Yasin	43	10
5	GURS	Aaron	18	0
6	VERBS	Fat	32	8
7	VERBS	Etha	7	0
8	VERBS	Chris	43	12
9	VERBS	Ines	10	0
10	VERBS	Patrice	43	8
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=LET(maxValue,MAXIFS(C1:C10,A1:A10,"GURS",D1:D10,0),FILTER(B1:B10,(A1:A10="GURS")*(C1:C10=maxValue)*(D1:D10=0)))
Dynamic array formulas.

Hope this helps!