Google Sheets: MAXIFS formula which returns adjacent column data?

[Chrizh]

Hi all. I'm struggling with a formula. I have a spreadsheet ('Base Stats') of data. Column B contains a name. Column M contains scores (calculated from values in other columns). Column N contains a rank (1-5).

On another sheet ('Testing') I would like to pull some of the data based on rank and score. I have formulas which will return the maximum score for each rank based on the name:

=MAXIFS('Base Stats'!M:M, 'Base Stats'!B:B, "Acorna", 'Base Stats'!N:N, "1")
=MAXIFS('Base Stats'!M:M, 'Base Stats'!B:B, "Acorna", 'Base Stats'!N:N, "2")
=MAXIFS('Base Stats'!M:M, 'Base Stats'!B:B, "Acorna", 'Base Stats'!N:N, "3")
=MAXIFS('Base Stats'!M:M, 'Base Stats'!B:B, "Acorna", 'Base Stats'!N:N, "4")
=MAXIFS('Base Stats'!M:M, 'Base Stats'!B:B, "Acorna", 'Base Stats'!N:N, "5")

This returns the correct maximum values for that rank. What I would also like the formula to do however, is also return some other information from the same row?

For example, taking the first formula above:

=MAXIFS('Base Stats'!M:M, 'Base Stats'!B:B, "Acorna", 'Base Stats'!N:N, "1")

Will return the value 358.2. This is pulled from column M row 8 on 'Base Stats'. I would also like the retrieve the values of column C and D row 8.

I think that I maybe need to use INDEX or QUERY for this. I've tried to do this with INDEX but it errors. My spreadsheet can be found at:

SpotRacers Base Stats List

Dashboard

docs.google.com

Your help is greatly appreciated

 

[Fluff]

How about

=ARRAY_CONSTRAIN(FILTER(SORT(FILTER('Base Stats'!C2:M3335,('Base Stats'!B2:B3335=B4)*('Base Stats'!N2:N3335=A4)),11,0),{1,1,0,0,0,0,0,0,0,0,1}),1,3)

 

[Chrizh]

How about

=ARRAY_CONSTRAIN(FILTER(SORT(FILTER('Base Stats'!C2:M3335,('Base Stats'!B2:B3335=B4)*('Base Stats'!N2:N3335=A4)),11,0),{1,1,0,0,0,0,0,0,0,0,1}),1,3)

Superb! Thank you. That works perfectly. I just now need to work out how to return blanks instead of #N/A because sometimes an entry is not available for every star rating (rank). I'll see if I can add an IFERROR somewhere.

 

[Fluff]

You're welcome & thanks for the feedback.