On 2002-11-01 11:09, Juan Pablo G. wrote:
Try an INDEX/MATCH combination:
=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))
The Index sets a range of data (A1:A10) and you tell it which row of that data you want to return eg
a1 = 1
a2 = 2
a3 = 3
=INDEX(A1:A3,2) will return 2
=INDEX(A1:A3,3) will return 3
Juan Pablo is using this in turn with MATCH (to determine the row to return from your index range) - he is finding the max value in the range, then matching it in the range to return the row number of the match
using the a1=1 etc example
=MAX(A1:A3) will return 3
=MATCH(MAX(A1:A3),A1:A3) will also return 3 as the value 3 (the max) is in row 3.
so
=INDEX(B1:B10,MATCH(MAX(A1:A3),A1:A3,0))
will read
=INDEX(B1:B10,3)
so will return the value in B3.
Make sense?