Hi,
I have a requirement to display the maximum scored students based on the marks. i can able to display only one student if more than one scored the same marks i want to display there name also.

example

sl.no studentname mark
1 john 90
2 paul 95
3 sam 80
4 michel 95

using this formula =INDEX(B:B,MATCH(MAX(C:C),C:C,0)); i can able to display the value only "paul". i want to display both paul and michel.

Please help us on this.

Thanks in Advance.

---
frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/

95

=MAX((G1:G21="paul")*(H1:H21))
An array formula so it must be entered using ctrl+shift+enter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"murali s" wrote in message
...
Hi,
I have a requirement to display the maximum scored students based on the
marks. i can able to display only one student if more than one scored the
same marks i want to display there name also.

example

sl.no studentname mark
1 john 90
2 paul 95
3 sam 80
4 michel 95

using this formula =INDEX(B:B,MATCH(MAX(C:C),C:C,0)); i can able to
display the value only "paul". i want to display both paul and michel.

Please help us on this.

Thanks in Advance.

---
frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/

A simple non-array, entire col formulas play which can deliver the expected
results
Assume names in B2 down, scores in C2 down (as posted)
In D2: =IF(C2=MAX(C:C),ROW(),"")
Copy D2 down to cover the max expected extent of data in col C, eg down to
D200?

Then in E2:
=IF(ROWS($1:1)COUNT($D:$D),"",INDEX(B:B,SMALL($D: $D,ROWS($1:1))))
Copy E2 across to F2, fill down by the smallest extent enough to cover the
max expected number of ties in the maximum score, say down to F10? Cols E and
F will return all the names & scores which tie with the maximum score, neatly
bunched at the top. Success? hit the YES below
--
Max
Singapore
xde
---
"murali s" wrote:
I have a requirement to display the maximum scored students based on the marks. i can able to display only one student if more than one scored the same marks i want to display there name also.

example

sl.no studentname mark
1 john 90
2 paul 95
3 sam 80
4 michel 95

using this formula =INDEX(B:B,MATCH(MAX(C:C),C:C,0)); i can able to display the value only "paul". i want to display both paul and michel.

Please help us on this.

Thanks in Advance.

---
frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/
.