Problems with Index Match Array returning duplicates

Question

I'm attempting to return the column name for the three lowest values in a set. At the moment if two values are the same it is duplicating and returning the first value, which I don't want. I need it to return three unique column names with the smallest values.

The values are inputted in row 6 but I want a list in O that returns the corresponding value in row 5 for the lowest three scores without duplicates.

The formulas I have tried are '

{=INDEX(E5:L5, MATCH(SMALL(E6:L6,{1;2;3}), E6:L6,0))}

and the following which gave me an N/A error.

=INDEX(E5:L5, MATCH(0, COUNTIF($A$1:A$1, E6:L6)+IF(E6:L6<>"", 1, 0), 0))

I'm currently using Excel 2016 so TRANSPOSE/SORT didn't work for me either. I'm at a loss.

The values are inputted between E6:L6 and I need the returning values to be in an array from O10.

Sample Data

Crossposted: https://www.excelforum.com/excel-formulas-and-functions/1416581-index-match-array-returning-duplicate-values.html

Answer 1

Create a "Helper Row" somewhere on your worksheet

AE6: =COUNTIF(E6:$L$6,E6)-1

and fill right to AL6

For largest 3 numbers:

=TRANSPOSE(INDEX($E$5:$L$5,,MATCH({1,2,3},RANK.EQ($E6:$L6,$E6:$L6,0)+$AE6:$AL6,0)))

For smallest 3 numbers:

=TRANSPOSE(INDEX($E$5:$L$5,,MATCH({1,2,3},RANK.EQ($E6:$L6,$E6:$L6,1)+$AE6:$AL6,0)))

If Excel 2016 does not have dynamic arrays, you can either:

• Enter it as an array (ctrl-shift-enter) across the three rows
• Use the INDEX function to return each individual element

Counts

Data

Results

Answer 2

For Excel-2016 try the following array formula-

=INDEX($E$5:$L$5,1,SMALL(IF(($E$6:$L$6>=MIN($E$6:$L$6))*($E$6:$L$6<=SMALL($E$6:$L$6,3)),COLUMN($E$6:$L$6)-COLUMN($D$5),""),ROW(1:1)))

Answer 3

This is not an elegant one but works

Presume that E23:L23 is the column header range E28:L28 is the data range

Cell O25 : =INDEX(A23:L23,1,(SMALL(E28:L28+COLUMN(E28:L28)/100,{1,2,3,4})-SMALL(E28:L28,{1,2,3,4}))*100)

and drag down to O28

Take care that index range start from column A!

This is for the first four but this you can expand or truncate with the array {1....4}. If more than 100 columns are used change the 100 value to 1000.