INDEX MATCH and MIN/SMALL to pull first and second smallest values from array

I'm using this formula to lookup data based on the lowest average.

Please Login or Register  to view this content.

The problem is that the minimum average is a two way tie, and I need to return both data sets. It just happens to have the same average.

I've tried using SMALL(array,1) and SMALL(array,2) in place of MIN above, but it gives me the same data set. I've also tried resizing using offset. See attached.

I am little bit unclear about your approach

This can be

=INDEX($I$3:$AF$17,$A3,MATCH(MIN($I$18:$AF$18),$I$18:$AF$18,0))

simplified as

=INDEX($I3:$AF3,,MATCH(MIN($I$18:$AF$18),$I$18:$AF$18,0))

But still I am unable to understand this part

MATCH(MIN($I$18:$AF$18),$I$18:$AF$18,0)

You could probably do this with a helper column to handle the tie-breaks using =A1+(countif($A$1:A4,A1)/100), copied down - use that for the min() ref

My favourite trick to deal with such situation would be:

I18:
=IF(ISERROR(AVERAGE(I3:I17)),"",AVERAGE(I3:I17)-COLUMN()/100000)

(as you see each average is decrised just by a tiny,tiny bit (more as we go further right. decreased and thus out of two columns with the same average, the left one will be reported first, and right will be reported as a second one with small(...,2)

You really do not need to care about rounding in this case - if it makes a problem (for instance in printing), just format the cell to show 1 decimal digit.

then in F3 MIN:
=INDEX($I$3:$AF$17,$A3,MATCH(MIN($I$18:$AF$18),$I$18:$AF$18,0))
and in G3 SMALL(..,2):
=INDEX($I$3:$AF$17,$A3,MATCH(SMALL($I$18:$AF$18,2),$I$18:$AF$18,0))

and copy both down.

Hi and thanks for the reply! I'm using this part of the formula MATCH(MIN($I$18:$AF$18),$I$18:$AF$18,0) to find which column to pull the data from.

The reason I'm not just using the MIN formula for each cell is that would give me the lowest value, but I'm after the lowest two values in my rows of averages to tell me which columns I'm interested in. The averages are on row 18. Does that make sense? BTW- I'm rummy tired and so I'm probably not making much sense...sorry!

Hi tlafferty,

Have you tried my solution?

OK, as you are tired (an I just got up - it is 8:46 AM here and I just finished my morning coffe) - please have a look on the attachment.

Edit: I started answering before coffe - you shall add rather than substract this small lineary increasing part. Anyway, the method works!