index?lookup?match?if?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rduffey - 30 Mar 2006 19:37 GMT

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

Reply to this Message

Domenic - 31 Mar 2006 13:59 GMT

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!

> I am making a spreadsheet to record compeditive times in an autocross.
> Colums a,b,and c, contain respectively driver number, driver name and
[quoted text clipped - 9 lines]
> second member of the tie to show up in second location. I'm trying to
> avoid a macro for simplicity sake. Please help. Thanks

Reply to this Message

rduffey - 31 Mar 2006 15:32 GMT

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

Signature

rduffey

Reply to this Message