Analyzing data and pulling specific data into other columns

Hi -

In column I, I just use the SMALL() function and the numbers in column A to get the 1st smallest time, the second smallest time, etc. That formula looks like:

=SMALL($C$3:$C$16,A3)

The one thing you need to be careful is there are zeroes entered into your table below the last contestant. So, if you expand the range to include those rows, get rid of the zeroes or they will float to the top of the SMALL function's choices.

The next column is to match up the names with the times. Normally that would be a simple INDEX/MATCH except you have several ties. So I have to use an Array formula. I use an IF statement to create an array of row numbers of all the contestants that have the same times. I use COUNTIF to pass to the SMALL function, which on the first instance of a given time it returns the smallest row number with that time. Then INDEX goes to that row and picks that name. On the second instance of that time, COUNTIF passes 2 to SMALL, which then gives INDEX the second smallest row. If there were multiple ties, this pattern will just continue (3rd smallest, 4th smallest, etc.) The formula looks like:

=INDEX($B$3:$B$16,SMALL(IF($C$3:$C$16=I3,ROW($C$3:$C$16)-ROW($2:$2),""),COUNTIF($I$2:I3,I3)))

Note this is an array formula so you will notice in the attached Excel spreadsheet it has curly braces { } around it. Google array formulas if you're not familiar with them.

Anyway, hope this helps.

Hi -

Sorry, I had meetings all day today. Just getting back to this.

You can simply insert a column to the left of the times. Highlight the range with the names in it, grab the edge of the range with the mouse and move the whole thing to the new column to the left of the times.