Top N List based on 2 conditions & returning adjacent cell text

Hello everybody!

I've been a lurker for quite some time, and so far I had always found an idea or the solution itself to my excel formula problems, until today.

I am sure the answer is in front of my eyes, but truth be told, my eyes (and brain) are quite burned out at the time.

What I am trying to obtain is a Top 5 list based on two conditions: 1) Makers of a specific country and 2) the largest Production.

To begin with, I use two different formulas. With one I have been able to correctly list the top 5 largest productions of country "JP". With the second one I want to reproduce the Maker name, which sits in the cell adjacent to the Country/Production cells.

The problem, as you will notice is that I am missing something from the second formula, given that it returns a value which I do not want. The Top 3rd returns FORD instead of TOYOTA, as they both share production value, but FORD is first in list, thus the result. I understand I need to include an additional condition to check for the country as well, but I am just too burned out and failing after each try.

Help will be greatly appreciated.

Than you!

Hello Kooey,

You need one more IF to check Col_B = JP, use this Array Formula,

=IF(F3<>"",INDEX($A:$A,SMALL(IF($C$3:$C$25=F3,IF($B$3:$B$25=$E$1,ROW($C$3:$C$25))),COUNTIF($F$3:F3,F3))),"")

Also, it is good to check E1 value has Top N records in data. Change E1 value to US, you can see the errors. In E3 use this,

=MIN(E2,COUNTIF(B:B,E1))

If occurrences of E1 value less than Top N value in E2 wouldn't give any errors. Then change <=$E$2 to <=$E$3 in the 1st formula.