findining maksimum of Loan amount with one single formula

Dears Excelforum users,

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

Please find attached example of my main file which I cannot upload because of its size. Consider that in my file, there are more periods and teams.

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

Looking forward to your answers.

Thanks in advance.

1) MAX() function to determine the highest loan amount MAX(loan amounts)
2) Find the position of that maximum in the column with a MATCH() function MATCH(MAX(...),loan amounts,0) MATCH() function help: https://support.microsoft.com/en-us/...9-533f4a37673a
3) Use INDEX() to return the corresponding team name from the team column =INDEX(loan teams,MATCH(...)) INDEX() function help: https://support.microsoft.com/en-us/...2-b56b061328bd

Will something like that work for you?

Originally Posted by Abbas Nabizada

Imagine data-sheet is consists of 4 columns ( Year, Months, Loan Teams ( Team1, Team 2 and etc. ), and Loan Amounts

Why should we "imagine" anything?

You might get better responses if you follow the instructions in the bright yellow banner at the top of these webpages and attach an example Excel file.

By "example", I mean: some representative data laid out the way you want (the "4 columns" that you imagine) and an indication of the desired result, created manually of course.

Originally Posted by Abbas Nabizada

Looking forward to your answers.

Hopefully they don't duplicate the responses in your posting at https://www.mrexcel.com/board/thread...ormula.1163235.

One way:

=AGGREGATE(14,6,$D$2:$D$19/($A$2:$A$19=2019)*($B$2:$B$19="December"),1)

OK>

=INDEX(C:C,MATCH(AGGREGATE(14,6,$D$2:$D$19/($A$2:$A$19=2019)*($B$2:$B$19="December"),1),D:D,0))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.