How to get the cell reference of the second smallest value from a range of data

Question

I need to get the cell reference (specifically I only need the column number) of the second smallest value from a range of data.Complicated by the fact that the values in the range can be the same. eg.

A B C D E    
1 5 2 1 8

Should return D as my column with the second smallest value.

The current part of my code is as below:

=SMALL('Sheet1'!C38:X38,2),'Sheet1'!C38:X38,0)

However this leaves me with the problem that if the second smallest value is the same as the first only the first will be matched and returned.

Is there any way to achieve this?

Answer 1

Use Aggregate to return the column number which you then would supply to an INDEX formula to return the other value:

=INDEX($A$3:$E$3,AGGREGATE(15,6,COLUMN($A$1:$E$1)/($A$1:$E$1=SMALL($A$1:$E$1,2)),MIN(2,COUNTIF($A$1:$E$1,SMALL($A$1:$E$1,2)))))