Find value in range and return data from another column

[McG_84]

Hi everyone

I have attached a file to make this easier. Basically I would like to search "Points Scored" (B2:W9) for the highest score (which I already have done) and when it is found return the team's name that is associated with it. Then do this for the second, third, etc. as it can be seen in the "Main" sheet. I'm thinking an index/find approach, but I just keep getting "N/A". I must be missing something. Any help would be greatly appreciated.


Thanks
McG.

[Speshul]

You mean highest scoring team per week? Or total?

This would be per week:

Place on sheet 'Points Scored'
=INDEX(A2:A9,MATCH(MAX(B2:B9),B2:B9))

[McG_84]

Thanks for your incredibly fast reply! I am looking to continuously have the top 10 scores (and bottom 10) for the entire year. So the list on the "Main" sheet would be like:
1. Team 5 - 419 (week #12)
2. Team 7 - 416.25 (week #1)
3. Team 5 - 408.25 (week #9)
etc.

Your response did give me an idea for more data to display, so thank you

[oeldere]

Since you use excel 2010 you can also use a pivot table to make a top 10 (or top 15).

[Speshul]

Use Control + Shift + Enter to place this formula.

NOTE! This wasn't originally written by me, I had a similar issue a while back and got help from these forums. I did modify the original formula to fit your sample and desired result though...

To get different scores, change the highlighted number with the rank you want.

I posted this;

=INDEX($A$2:$A$9, LOOKUP(SUM((($B$2:$W$9=LARGE(B2:W9,1)) * ROW($B$2:$W$9))), {1,2,3,4,5,6,7,8})-1) & " - " &LARGE(B2:W9,1)&", ("&INDEX($B$1:$W$1, LOOKUP(SUM((($B$2:$W$9=LARGE(B2:W9,1)) * COLUMN($B$2:$W$9))), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22})-1)&")"
(Original formula by JBeaucaire in this thread)

but use this one instead

= INDEX(A2:A9,MATCH(TRUE,MMULT(--(B2:W9=LARGE(B2:W9,1)),TRANSPOSE(COLUMN(B2:W9)^0))>0),0)
&" - "&LARGE(B2:W9,1)&", ("&INDEX(B1:W1,0,MATCH(TRUE,MMULT(TRANSPOSE(--(B2:W9=LARGE(B2:W9,1))),(ROW(B2:W9)^0))>0))&")"

Original formula by Bob Phillips in this thread

[McG_84]

This works perfectly! Thank you so much. Both options. You're both great.

[McG_84]

OK. Another challenge. How would this work to get the 5 lowest scoring teams?

[McG_84]

Sorry. Never mind. I just cleared any "0" instances and I got it working. Thanks again