Find the player at a specified position for a specified team but with the most plate appearances

[The Ruff Report]

I'm trying to return the player with the most plate appearances for a specified team and position. The formula in the pic sort of works - it's finding the right value for plate appearances. My issue is I want to return the player's name though.

I could use Index/Match or similar for the example in the pic and it would work fine. The problem is if I change the position to SS or 3B, that Index/Match would return the wrong player.

Any thoughts? I'm using 365 and I would like to avoid using any helpers, CSE formulas, or a second set of formulas. The actual data is about 1500 rows and there are multiple instances of players with the same PAs.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[The Ruff Report]

Not quite but I think you're close. In the example, I'd want the pink cell to return "Mauricio Duban". He is the 2B on HOU with the most plate appearances. The three other players at 2B would be ignored.

If I change cell A1 to "SS" and kept A2 "HOU", I'd want to return "Jeremy Pena" only, not "Javier Baez"

I'm continuing to work on it on my end as well

 

[Stephen_IV]

I think this will do it!

=INDEX($A$5:$A$14, MATCH(MAX(IF(($C$5:$C$14=$A$1)*($B$5:$B$14=$A$2), $D$5:$D$14)), IF(($C$5:$C$14=$A$1)*($B$5:$B$14=$A$2), $D$5:$D$14), 0))

 

[The Ruff Report]

Awesome. Works perfectly. I'm discovering I need to get better at formulas like this with arrays.

Thanks again

 

[Eric W]

You can also do it like this with the array functions:

=INDEX(SORT(FILTER(A5:D14,(B5:B14=A2)*(C5:C14=A1)),4,-1),1,1)

 

[The Ruff Report]

Yep, that works too Eric. Very helpful.

I also need to find the team's 3 Outfielders (position "OF") with the most Plate Appearances (PA) but I'll try and figure that out using your answers as guidance. The data doesn't parse out outfielders by Left, Center, Right.