( Interesting question ) Finding best Loan team with single formula

[abbasnabizada]

Dears users,

I have a question for you which I can't solve.

Imagine data-sheet is consists of 4 columns ( Year, Months, Loan Teams ( Team1, Team 2 and etc. ), and Loan Amounts. Teams have more than one loan amount for each month ( For example 2019- Nov, Team 1 : 500$, 600$, 700$, Team 2: 600$,500$ and etc.

Question: How to get the name of the best (highest total loan amount ) Loan team in Dec-2019 with one single formula.

Looking forward to your answers.

Thanks in advance.

 

[kweaver]

Welcome to Mr. Excel,

It's unclear (to me, at least) how the data is structured. It always helps if you use XL2BB and show your example and desired result.
Does it look something like this?

PS: is this a homework problem?

Year	Month	Team	Amount
2019​	Nov	Team1	500​
2019​	Dec	Team1	600​
2020​	Jan	Team1	700​
2019​	Nov	Team2	600​
2019​	Dec	Team2	500​

 

[abbasnabizada]

Welcome to Mr. Excel,

It's unclear (to me, at least) how the data is structured. It always helps if you use XL2BB and show your example and desired result.
Does it look something like this?

Welcome to Mr. Excel,

It's unclear (to me, at least) how the data is structured. It always helps if you use XL2BB and show your example and desired result.
Does it look something like this?

PS: is this a homework problem?

Year	Month	Team	Amount
2019​	Nov	Team1	500​
2019​	Dec	Team1	600​
2020​	Jan	Team1	700​
2019​	Nov	Team2	600​
2019​	Dec	Team2	500​

PS: is this a homework problem?

Year	Month	Team	Amount
2019​	Nov	Team1	500​
2019​	Dec	Team1	600​
2020​	Jan	Team1	700​
2019​	Nov	Team2	600​
2019​	Dec	Team2	500​

Thanks for your reply.

More like the below table.

Please consider that it is just a little example of my real file. There are 15 teams in my data.

Book2
	A	B	C	D
1	Year	Month	Team	Loan Amounts
2	2019	November	Team 1	400
3	2019	November	Team 1	800
4	2019	November	Team 1	1600
5	2019	November	Team 2	500
6	2019	November	Team 2	400
7	2019	November	Team 2	1300
8	2019	December	Team 1	480
9	2019	December	Team 1	750
10	2019	December	Team 1	900
11	2019	December	Team 2	500
12	2019	December	Team 2	720
13	2019	December	Team 2	800
14	2020	January	Team 1	850
15	2020	January	Team 1	740
16	2020	January	Team 1	600
17	2020	January	Team 2	400
18	2020	January	Team 2	500
19	2020	January	Team 2	900
Sheet1

 

[kweaver]

How about:

Book5
	A	B	C	D	E	F	G
1	Year	Month	Team	Amount			Nov
2	2019	Nov	Team1	500			Team2
3	2019	Dec	Team1	600
4	2020	Jan	Team1	700
5	2019	Nov	Team2	1000
6	2019	Dec	Team2	900
7	2019	Dec	Team3	400
8	2019	Nov	Team4	700
9	2019	Dec	Team4	950
Sheet3

Cell Formulas
Range	Formula
G2	G2	=INDEX(C2:C50,MATCH(MAX(D2:D50*(G1=B2:B50)),D2:D9*(G1=B2:B50),0))
Press CTRL+SHIFT+ENTER to enter array formulas.