CODEWITHSUNDEEP
excelexcel-formula
Sokudan
Sokudan

Reputation: 1

Using small function to return both repeating values in excel 2013

I'm trying to return column headings for the lowest 3 values with the formula below, one in each cell to use for lookups. But if there are entries with the same value it just returns the 1st value multiple times.

INDEX($F$1:$CD$1,0,MATCH(SMALL(range,x),range,0))

Is there any way to alter this to get the output I need, or will the data have to be manually reviewed if there is duplication?

Example my input has apple banana and orange all at 4, my formula will just return apple all 3 times. Ideally I would need them to return apple, banana, orange.

Thanks

Upvotes: 0

Views: 648

Answers (2)

John Bauer
John Bauer

Reputation: 1

There is an inelegant but effective way: make each number unique by adding the place in the list as a decimal to that number, in a separate column. Use that "unique number" column to small/large compare. So apple, banana and orange are all three "1," they become 1.1, 1.2 and 1.3. Thus unique, so small/large has no problem treating each as identifiable. If you need to display the "1"s, just use the integer function on the unique column.

Upvotes: 0

z32a7ul
z32a7ul

Reputation: 3777

I see. The problem is that SMALL returns the third smallest value, not the three smallest values if you give 3 as an argument to it. But you may write {1,2,3} to get the latter result.

If you want the headers below each other, put this array formula (Ctrl+Shift+Enter) into, say A1:A3:

=TRANSPOSE(INDEX($F$1:$CD$1,0,MATCH(SMALL($F$2:$CD$2,{1,2,3}),$F$2:$CD$2,0)))

If you want the headers next to each other, put this array formula (Ctrl+Shift+Enter) into, say A1:C1:

=INDEX($F$1:$CD$1,0,MATCH(SMALL($F$2:$CD$2,{1,2,3}),$F$2:$CD$2,0))

Upvotes: 0

Related Questions