Embedded Range or Array Lookup

[Gimics]

Hey pro team,

I'm looking for advice to solve two lookup-related challenges from the same data set. I've started a fantasy league for Formula 1 with some friends and would like to capture the results of our scoring pool for each race. I've created a tracker spreadsheet that lists our "Players", their "Picks", and the scores of their picks. What I would like to do with two separate formulas is 1: Lookup and return the Pick with the most points from that race week, and 2: Lookup and return the Player with the most points from that race week.

Here's an example of how the data is structured:

	A	B	C	D
1	Race	Player	Pick	Points
2	Bahrain	Dwight	VER	10
3	Bahrain	Jim	PER	15
3	Bahrain	Michael	LEC	0
4	Bahrain	Dwight	SAI	5
5	Bahrain	Jim	RUS	8
6	Bahrain	Michael	ALO	6
7
8	Saudi	Dwight	LEC	15
9	Saudi	Jim	VER	10
10	Saudi	Michael	PER	5
11	Saudi	Dwight	SAI	8
12	Saudi	Jim	ALO	6
13	Saudi	Michael	RUS	12

And here's a table of the results summary where I am looking for help with the formulas:

	A	B	C
1	Race	Player Winner	Pick Winner
2	Bahrain	Formula 2 (Result: Jim)	Formula 1 (Result: PER)
3	Saudi	Formula 2 (Result: Dwight)	Formula 1 (Result: LEC)

Formula 1: Pick Winner - Just need a simple way of looking up which "Pick" driver had the most (Max) points for each race. Would need to lookup the race (ex. "Bahrain"), then fine the max value in column "D" and return the "Pick".
The result for C2 should be PER and the result for C3 should be LEC.

Formula 2: Player Winner - Similar to Formula 1, but need to "sum" the points received for each Player, THEN apply the "Max" formula to find the most points, THEN return the Player name.
The result for B2 should be Jim, and the result for B3 should be Dwight.

Would be amazing to find a way to do this in a single formula, instead of with helper tables or columns - but I know that's going to be challenging.

Thanks in advance!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Race	Player	Pick	Points
2	Bahrain	Dwight	VER	10
3	Bahrain	Jim	PER	15
4	Bahrain	Michael	LEC	0
5	Bahrain	Dwight	SAI	5
6	Bahrain	Jim	RUS	8
7	Bahrain	Michael	ALO	6
8
9	Saudi	Dwight	LEC	15
10	Saudi	Jim	VER	10
11	Saudi	Michael	PER	5
12	Saudi	Dwight	SAI	8
13	Saudi	Jim	ALO	6
14	Saudi	Michael	RUS	12
15
16
17
Sheet1

Fluff.xlsm
	A	B	C
1	Race	Player Winner	Pick Winner
2	Bahrain	Jim	PER
3	Saudi	Dwight	LEC
4
Sheet2

Cell Formulas
Range	Formula
B2:C3	B2	=TAKE(SORT(FILTER(Sheet1!$B$2:$D$200,Sheet1!$A$2:$A$200=A2),3,-1),1,2)
Dynamic array formulas.

 

[Gimics]

Thanks Fluff - I don't see any summation happening for Formula 2 (Player Winner) though; I think your "sort" is just finding Jim and Dwight because they picked the first-place driver - perhaps I should tweak the data also that the Player whose Pick came in first place, isn't necessarily the winner for the week.

Below I've adjusted the scoring slightly so that in Bahrain Dwight picked the first place driver, but Jim actually has the highest points overall. The formula should return "Jim" for Bahrain, but it doesn't:

Book1
	A	B	C	D
1	Race	Player	Pick	Points
2	Bahrain	Dwight	VER	15
3	Bahrain	Jim	PER	10
4	Bahrain	Michael	LEC	5
5	Bahrain	Dwight	SAI	0
6	Bahrain	Jim	RUS	8
7	Bahrain	Michael	ALO	6
8
9	Saudi	Dwight	LEC	15
10	Saudi	Jim	VER	10
11	Saudi	Michael	PER	5
12	Saudi	Dwight	SAI	8
13	Saudi	Jim	ALO	6
14	Saudi	Michael	RUS	12
15
16
17	Race	Player Winner	Pick Winner
18	Bahrain	Dwight	VER
19	Saudi	Dwight	LEC
Sheet1

Cell Formulas
Range	Formula
B18:C19	B18	=TAKE(SORT(FILTER(Sheet1!$B$2:$D$14,Sheet1!$A$2:$A$14=A18),3,-1),1,2)
Dynamic array formulas.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Race	Player	Pick	Points		Race	Player Winner	Pick Winner
2	Bahrain	Dwight	VER	15		Bahrain	Jim	VER
3	Bahrain	Jim	PER	10		Saudi	Dwight	LEC
4	Bahrain	Michael	LEC	5
5	Bahrain	Dwight	SAI	0
6	Bahrain	Jim	RUS	8
7	Bahrain	Michael	ALO	6
8
9	Saudi	Dwight	LEC	15
10	Saudi	Jim	VER	10
11	Saudi	Michael	PER	5
12	Saudi	Dwight	SAI	8
13	Saudi	Jim	ALO	6
14	Saudi	Michael	RUS	12
15
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=LET(u,UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=F2)),TAKE(SORT(HSTACK(u,SUMIFS(D:D,A:A,F2,B:B,u)),2,-1),1,1))
H2:H3	H2	=TAKE(SORT(FILTER($C$2:$D$100,$A$2:$A$100=F2),2,-1),1,1)

 

[Gimics]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Race	Player	Pick	Points		Race	Player Winner	Pick Winner
2	Bahrain	Dwight	VER	15		Bahrain	Jim	VER
3	Bahrain	Jim	PER	10		Saudi	Dwight	LEC
4	Bahrain	Michael	LEC	5
5	Bahrain	Dwight	SAI	0
6	Bahrain	Jim	RUS	8
7	Bahrain	Michael	ALO	6
8
9	Saudi	Dwight	LEC	15
10	Saudi	Jim	VER	10
11	Saudi	Michael	PER	5
12	Saudi	Dwight	SAI	8
13	Saudi	Jim	ALO	6
14	Saudi	Michael	RUS	12
15
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=LET(u,UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=F2)),TAKE(SORT(HSTACK(u,SUMIFS(D:D,A:A,F2,B:B,u)),2,-1),1,1))
H2:H3	H2	=TAKE(SORT(FILTER($C$2:$D$100,$A$2:$A$100=F2),2,-1),1,1)

Thanks again Fluff - this definitely works, but I've never used TAKE() LET() UNIQUE() or HSTACK() before .. and some of these appear to be newer functions that aren't working on older versions of Excel that my friends (the "Players" use)?

Any thoughts on using any more common formulas, before I mark your response as the solution?

 

[Fluff]

It just depends what version of Excel they are using.