Finding the column with the smallest value..

Morning, hope someone can help.

I'm currently using the SMALL formula to find the 3 smallest values between D4:J4, which works perfectly... but now I need the results to display either the column number/letter or the column heading, instead of the actual smallest values.

Hope I've explained this clearly, thanks in advance,
Greg.

Sorry I forgot to mention - I need the three lowest columns, similar to what I can do with the SMALL formula.. i.e =SMALL(D4:J4,2) - but need this to show which column number/letter this is in, rather then the values..

For the smallest value, try

=INDEX($D$1:$J$1,MATCH(SMALL($D$4:$J$4,1),$D$4:$J$4,0))

edit to suit for the second and third samllest values.

This still shows the actual smallest value - I want it to show the result as the column letter or number (i.e F or 6..) or even better Leeds (as the column is headed)...

Originally Posted by gbzilla

or even better Leeds (as the column is headed)...

That is what the formula should be doing, I assumed headers are in D1:J1.

Jason - perfect, apologies first time round when I replied I hadn't copied your formula and just pasted my original formula back in!

That works perfect, exactly what I was after - thanks!

Originally Posted by gbzilla

I hadn't copied your formula and just pasted my original formula back in!

Lol an easy mistake to make, at least you managed to find where you went wrong easily.

Glad it works , thanks for the feedback.

I'm using this formula at the mo:

=CELL("col",OFFSET(D4:J4,0,MATCH(MIN(D4:J4),D4:J4,0)-1))

But I can't adjust that to show the second and third lowest columns....

Originally Posted by Ace_XL

=INDEX($D$1:$J$1,1,MATCH(SMALL($D$4:$J$4,1),$D$4:$J$4,0))

That wouldn't affect the result, you only need to define row and column with a 2D array, with a 1D array the behaviour of index would be the same as transposing the data.