Returning multiple adjacent cell results from an m

Question

I'm trying to setup a formula that will return the contents of an related cell (my related cell is on another sheet) from the smallest 2 results in an array. This is what I'm using right now.

=INDEX('Sheet1'!$A$40:'Sheet1'!$A$167,MATCH(SMALL(F1:F128,1),F1:F128,0),1)

And

=INDEX('Sheet1'!$A$40:'Sheet1:!$A$167,MATCH(SMALL(F1:F128,2),F1:F128,0),1)

The problem I've run into is twofold.

First, if there are multiple lowest results I get whichever one appears first in the array for both entries.

Second, if the second lowest result is duplicated but the first is not I get whichever one shows up on the list first, but any subsequent duplicates are ignored. I would like to be able to display the names associated with the duplicated scores.

Answer 1

You will have to adjust the k parameter of the SMALL function to raise the k according to duplicates. The COUNTIF function should be sufficient for this. Once all occurrences of the top two scores are retrieved, standard 'lookup multiple values' formulas can be applied. Retrieving successive row positions with the AGGREGATE¹ function and passing those into an INDEX of the names works well.

    

The formulas in H2:I2 are,

=IF(SMALL(F$40:F$167, ROW(1:1))<=SMALL(F$40:F$167, 1+COUNTIF(F$40:F$167, MIN(F$40:F$167))), SMALL(F$40:F$167, ROW(1:1)), "")   '◄ H2
=IF(LEN(H40), INDEX(A$40:A$167, AGGREGATE(15, 6, ROW($1:$128)/(F$40:F$167=H40), COUNTIF(H$40:H40, H40))), "")   '◄ I2

Fill down as necessary. The scores are designed to terminate after the last second place so it would be a good idea to fill down several rows more than is immediately necessary for future duplicates.

---

¹ _{The AGGREGATE function was introduced with Excel 2010². It is not available in earlier versions.}
² _{Related article for pre-xl2010 functions - see Multiple Ranked Returns from INDEX().}

Answer 2

The following formula will do what I think you want:

=IF(OR(ROW(1:1)=1,COUNTIF($E$1:$E1,INDEX(Sheet1!$A$40:$A$167,MATCH(SMALL($F$1:$F$128,ROW(1:1)),$F$1:$F$128,0)))>0,ROW(1:1)=2),INDEX(Sheet1!$A$40:$A$167,MATCH(1,INDEX(($F$1:$F$128=SMALL($F$1:$F$128,ROW(1:1)))*(COUNTIF($E$1:$E1,Sheet1!$A$40:$A$167)=0),),0)),"")

NOTE:

1. This is an array formula and must be confirmed with Ctrl-Shift-Enter.
2. There are two references $E$1:$E1. This formula assumes that it will be entered in E2 and copied down. If it is going in a different column Change these two references. It must go in the second row or it will through a circular reference.

What it will do

If there is a tie for first place it will only list those teams that are tied for first.

If there is only one first place but multiple tied for second places it will list all those in second.

So make sure you copy the formula down far enough to cover all possible ties. It will put "" in any that do not fill, so err on the high side.

To get the Scores use this simple formula, I put mine in Column F:

=IF(E2<>"",SMALL($F$1:$F$128,ROW(1:1)),"")

Again change the E reference to the column you use for the output.

I did a small test: