Find max value in one column and return the value of corrosponding cell in different column

[paul.mullan]

Hi All,

Haveing a bad excel day - I am sure I have done this before but
racking my brains and can't remember!!

I have a list of scores for various tests listed by agegroup. I am
trying to summarise the data on a seperate worksheet to list who
attained the maximum value in each test for each agegroup. So using
the example below of the raw data I am trying to use a function to
find the MAX of T1 for age 11 (22) and age 12 (10). What I am trying
to do is use a function to list the owner of these MAX values listed
in column B

A B C D
Age Name T1 T2
11 George 10 5
11 Dave 15 2
11 Jim 22 1
12 Paul 5 8
12 James 4 10
12 Dylan 10 11

Can I apply a similar function to list the 'owner' of the max value?

Thanks in advance
Paul

 

[paul.mullan]

Ah, I think I did something similar using the OFFSET function
before . . .

I have tried using this combined with the MAX function to list the
reference value. An example of what I have tried in relation to the
data above would be. However I am told my formula contains an error.

=OFFSET(MAX(C2:C4),0,-1,1,1)


I might be going down completely the wrong road! Any suggestions would
be gratefully received

Cheers
Paul

 

[vezerid]

Assuming the table starts at A1 (Age label) and occupies cells A17.
A11 contain headers.

A11 contains 11, A12 contains 12, B10 contains T1, C10 contains T2
(cross tabulation)

In B11, *array* formula (commit with Shift+Ctrl+Enter)

=INDEX($B$2:$B$7,MATCH(MAX(IF($A$2:$A$7=$A11,INDEX($A$2:$D$7,0,MATCH(B
$10,$A$1:$D$1,0)))),IF($A$2:$A$7=$A11,INDEX($A$2:$D$7,0,MATCH(B$10,$A
$1:$D$1,0)),0),0))

Copy through the range B11:C12

HTH
Kostis Vezerides

 

[Max]

One way

Array-entered (press CTRL+SHIFT+ENTER):
=INDEX(B2:B7,MATCH(MAX(IF(A2:A7=11,C2:C7)),IF(A2:A7=11,C2:C7),0))
will return the name: Jim, who has the max score for age group: 11 for T1

Note that in the event of any ties in the max score, the expression will
return the first name (the one higher up in the list)

 

[Roger Govier]

Hi Paul

I entered in G1 "T1" and H1 "T2" (without the quotes)
In F2 I entered 11 and in F3 12.

The array entered formula in G2
{=MAX(IF($B$2:$B$7=$F2,D$2$7,""))}
copied to H2 and G3:H3
returned the maximum values for each age group and each test.

Array formulae are created and edited using Control+Shift+Enter (CSE) not
just Enter.
When you use CSE, Excel will create the curly braces { } around the
formula. Do not type them yourself.

In G5 enter (normal enter, not array)
=INDEX($C$2:$C$7,MATCH(G2,D$2$7,0))
and copy to H5, G6:H6
to return the corresponding names

 

[Sandy Mann]

Just another option:

Age 11:

=INDEX(B2:B7,MATCH(MAX((A2:A7=11)*C2:C7),(A2:A7=11)*C2:C7))

Age 12:

=INDEX(B2:B7,MATCH(MAX((A2:A7=12)*C2:C7),(A2:A7=12)*C2:C7))

As witht the others, entered as an array formula with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk