Find the Max and the Min values with Index and Match function for each row

[sumit dey]

Hi Experts,

I am trying to find out the Max and the Min values for each row for student 1 to Student 11. When I select the student number on cell M4 the Min and Max value cells for the student should fetch on the cells M15 and M16 respectively.

I tried the below formula but it's showing #N/A (refer the attached file).

=INDEX(B9:I20,MATCH(MAX(B10:H10,0),0))

For eg when I select Student 1 the Max value should be 99 and the Min value should be 33.

I am not using excel for quite sometime now and your help would be very helpful in refreshing my excel skills.

Note: I have attached the file under manage attachment although I can't see the attachment while writing this section do let me know of the attachment is unavailable.

Thanks in adavance

[Glenn Kennedy]

Try:

=MAX(INDEX($C$4:$H$14,MATCH(M4,MatchRows,0),))

[etaf]

edit. ignore already answered as making my post

does this work for you,
=MIN(IF(MatchRows=M4,C4:H14))
=MAX(IF(MatchRows=M4,C4:H14))

you are showing 2003/2007 is that the latest version you are using ?

[JohnTopley]

Try

=MIN(INDEX($C$4:$H$14,MATCH($M$4,MatchRows,0),0))

=MAX(INDEX($C$4:$H$14,MATCH($M$4,MatchRows,0),0))

[sumit dey]

Hi Glenn,

That worked like a charm....thank you!

[sumit dey]

Thank you John that worked for me!

[JohnTopley]

Please mark thread as SOLVED ("Thread Tools" at top of threads).