index?lookup?match?if?

  • Thread starter Thread starter rduffey
  • Start date Start date
R

rduffey

I am making a spreadsheet to record compeditive times in an autocross.
Colums a,b,and c, contain respectively driver number, driver name an
car number. More than one driver can drive the same car. Colum
d,e,f,and g contain additional data. column H contains imput of eac
driver's time.
I have used SMALL(h2:h39,1) and SMALL(h2:h39,2) etc. to determine th
fastest times in order. This gives me the fastest times in order, The
below that, use MATCH( cell where "small" is,a2:b39,0etc) to matc
driver number and name to fastest time. I have the problem of a tie i
time. Small picks the tieing times and records them but "small" an
"match" only go back to the first driver number/name. I want th
second member of the tie to show up in second location. I'm trying t
avoid a macro for simplicity sake. Please help. Thank
 
Assuming that A2:H39 contains the data, try the following...

I2, copied down:

=RANK(H2,$H$2:$H$39,1)+COUNTIF($H$2:H2,H2)-1

J1:

=MAX(I2:I39)

K2, copied down and to Column L:

=IF(ROWS(K$2:K2)<=$J$1,INDEX(A$2:A$39,MATCH(ROWS(K$2:K2),$I$2:$I$39,0)),"
")

M2, copied down:

=IF(ROWS(M$2:M2)<=$J$1,INDEX(H$2:H$39,MATCH(ROWS(M$2:M2),$I$2:$I$39,0)),"
")

Hope this helps!
 
Thanks, Domenic.
Looks like your solution will work great for me. Now I've got some
expanding and cleaning to do.
 
Back
Top