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

Aggregate() may help. Post few of your sample data then show your desired output. — Harun24hr
– Harun24hr, Commented Dec 15, 2023 at 9:34
Have a look at this and give it a vote if it helps, it should do : stackoverflow.com/a/58640367/4961700 — Solar Mike
– Solar Mike, Commented Dec 15, 2023 at 10:15

Ron Rosenfeld · Accepted Answer · 2023-12-15 12:44:51Z

0

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

edited Dec 15, 2023 at 12:44

answered Dec 15, 2023 at 12:19

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harun24hr · Accepted Answer · 2023-12-15 10:14:48Z

0

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)))

answered Dec 15, 2023 at 10:14

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

3 Comments

Dreadpiraterai Over a year ago

I tried this, but it is just returning SD (first column) across all three return values

Harun24hr Over a year ago

Did you press CRTL+SHIFT+ENTER after putting formula to cell?

Dreadpiraterai Over a year ago

Yes. But SD is shown on all three cells that I selected.

Black cat · Accepted Answer · 2023-12-15 11:01:05Z

0

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.

answered Dec 15, 2023 at 11:01

Black cat

7,6358 gold badges69 silver badges99 bronze badges

Collectives™ on Stack Overflow

Problems with Index Match Array returning duplicates

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related