Using MAX and INDEX together

[doughnut_jimmy]

Hi,

First post here as i have usually been able to find answers to my issues before in previous posts. Fantastic site and helped me no end of times.

What i am trying to do is return the max value for each corresponding name, all in different cells.

Martin	Martin	Bob	Martin	Bob	Jay	Simon	Mark	Tim	Giles
140	100	20	10	50	70	70	150	50	30

[doughnut_jimmy]

To expand on that, i have a list of everybody's name in other columns, and i want to put a formula under each name that will return me their biggest win.

[Special-K]

Assuming the list is in column K

in L1
=MAX(IF(A$1:J$1=K1,A$2:J$2,""))
and copy down the column

NOTE: Array formula, use Ctrl-Shift-Enter

[oeldere]

Using the max function in the pivot table.

See the attached file.

[doughnut_jimmy]

Thanks guys, it was the CTRL+SHIFT+ENTER that was doing me over. I'm not familiar with array formulas and the need for that to make it work.

Thanks again.