Combining Index Match and max functions

[ifu06416]

Hi all,

I'm having some difficulty combining the index, match & max functions.

I have a column of 55 names (A2:A56) and 55 rows of these peoples 12 weeks performance (B2:M56), I would like to return the name of the person with the top performance across the 12 week period.

So far I have this formula which seem to work fine for returning the top performance for week 1 (B2:B56). However when I extend the formula to include all 12 weeks (B2:M56) the get an #NA result.

=INDEX(A2:A56,MATCH(MAX(B2:B56),B2:B56,0))


Any thoughts?

Regards,

John.

[FDibbins]

Hi, welcome to the forum

It wont work that way, you would need to ID the column that the max is in.

can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[ifu06416]

hi there,

here is the file

john

[azumi]

Im adding column total for easier calculation...

[FDibbins]

You have multiple "max" scores in many columns, so I took this approach.

N

O

P

Q

R

S

T

U

V

W

X

Y

7

Week1

Week2*

Week3

Week4

Week5*

Week6*

Week7*

Week8*

Week9*

Week10*

Week11*

Week12*

8

33

35

35

35

35

35

35

35

35

35

35

35

9

Person 1

Person 13

Person 12

Person 16

Person 31

Person 1

Person 54

Person 31

Person 3

Person 49

Person 11

Person 3

10

Person 34

Person 15

Person 3

Person 30

11

Person 43

Person 37

Person 25

12

Person 50

Person 54

Person 45

13

14

15

16

17

18

1. Create a small table with the same headings as in your table.
N8=MAX(B2:B56)
copied across
N9=IFERROR(INDEX($A$2:$A$56,SMALL(IF(B$2:B$56=N$8,ROW($A$2:$A$56)-1),ROWS($A$1:A1))),"")
This is an ARRAY formula...
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Copy this down and across as needed

[oeldere]

Or with a pivot table for (also the rank) of the persons.