League Table Lookup

**dpadmab86** · 06-09-2011, 06:07 AM

Hi, I have an excel document, its like a league table for a fantasy sports league.

Each week new scores will be added to the totals for players and we will then at the end of each month pay the winning player.

However, I've got the sum section sorted, I just don't know how to get a cell to run a function that will search through a list of say 20 peoples names (column a) view their score (changes each month, august - column C, september- column D ...etc...) and then display the persons name who is in the lead in a seperate cell.

Is this even possible ? If so, could anybody help, any help would be greatly appreciated

**brokenbiscuits** · 06-09-2011, 09:51 AM

Assuming your table of scores is in range A1:K21, with names of the months in row 1, and the current month in cell A24:

=INDEX(A1:K21,MATCH(MAX(INDIRECT(ADDRESS(1,MATCH(A24,1:1,0))&":"&ADDRESS(21,MATCH(A24,1:1,0)))),INDIRECT(ADDRESS(1,MATCH(A24,1:1,0))&":"&ADDRESS(21,MATCH(A24,1:1,0))),0),1)

Is that what you were after?

**dpadmab86** · 06-10-2011, 05:30 AM

Hi,

thanks for the reply, I've tried to implement it, but I can't get it to work properly. The cell just loads up #n/a ... and then when I've done it on a second sheet using the cell locations etc... you used, i got #name.

I've attached the spreadsheet so that you can see what I mean, thanks again for the help.

sorry if its something really simple that I've not grasped, I've never really been too good with excel. Thanks again for any help at all here :-) It's greatly appreciated

**brokenbiscuits** · 06-10-2011, 07:28 AM

Sorry about that, there was a rogue space in the fomula:

=INDEX(A1:K21,MATCH(MAX(INDIRECT(ADDRESS(1,MATCH(A24,1:1,0))&":"&ADDRESS(21,MATCH(A24,1:1,0)))),INDIRECT(ADDRESS(1,MATCH(A24,1:1,0))&":"&ADDRESS(21,MATCH(A24,1:1,0))),0),1)

Does that work now?

EDIT: Ok, this website seems intent on keeping the space, which is really weird. But basically there's a space showing between the I and the R in the second INDIRECT - remove this and it should work?

EDIT AGAIN: Just looked at the way you have your table set up, and the following would work better:

=INDIRECT(ADDRESS(MATCH(MAX(M6:M25),M1:M25,0),1))