Look up query?

[ipsdan]

I have a list of names in A:A and a list of scores in E:E. I have used MAX on column E to find the high score and copied it to another cell - say - D100. In D99 I want a formula that will put the name of the person who achieved the high score that is in cell D100. My efforts have so far returned rather pitiful results. Any help appreciated...

 

[P Sitaram]

Assuming that entries in A:A correspond to scores in E:E,

=INDEX(A:A,MATCH(MAX(E:E),E:E,0))

 

[ipsdan]

Great - thanks a lot!

 

[Aladin Akyurek]

I have a list of names in A:A and a list of scores in E:E. I have used MAX on column E to find the high score and copied it to another cell - say - D100. In D99 I want a formula that will put the name of the person who achieved the high score that is in cell D100. My efforts have so far returned rather pitiful results. Any help appreciated...

aaTop1 ipsdan.xls
	A	B	C	D
1			Max	35
2			Top	1
3			Ties	2
4	Name	Score	Rank	Top List
5	dawn	23	6	damon
6	damon	35	1	christine
7	bob	25	5	john
8	chris	22	7
9	christine	35	2
10	ian	32	4
11	john	35	3
12
Data

Formulas...

C5, copied down:

=RANK(B5,$B$5:$B$11)+COUNTIF(B5:$B$5,B5)-1

D1:

=MAX(B5:B11)

D2: 1

Means: Give me a Top 1 list.

D3:

=MAX(IF(INDEX(B5:B11,MATCH(D2,C5:C11,0))=B5:B11,C5:C11))-D2

which must be confirmed with control+shift+enter instead of just with enter.

This formula adjusts dynamically the number of names achieving the same max score.

D5, copied down:

=IF(ROW()-ROW($D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$11,MATCH(ROW()-ROW($D$5)+1,$C$5:$C$11,0)),"")