Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a table in Excel.

Can anyone help me find the column number of the cell where the min value of the row is?

Example: 200 is the min value in row1 and has the column number 1. Is there a function that returns the column number of the min value?

       col1  col2  col3  col4  col5 

row1 - 200 - 300 - 400 - 500 - 600

row2 - ... - ... - ... - ... - ...
share|improve this question
    
Check out ozgrid.com/forum/showthread.php?t=89269 -- it's for a similar problem, but with rows instead of columns. I expect you can probably edit it to match your needs. –  Mala Jul 22 '12 at 17:17
    
just for clarification - do you really need the number of the column, or is it sufficient to highlight the cell with the lowest value? –  guenter Jul 22 '12 at 17:22
    
Do you need the lowest column number for a range of rows combined? if not, then @kopischke offered, per row what works –  datatoo Jul 22 '12 at 18:28

1 Answer 1

up vote 5 down vote accepted

Assuming your data is in columns A to D, the following formula in E will return the relative numeric index of the smallest value (with 1 = 1st column = A, 2 = 2nd column = B, etc.) for row 1:

=MATCH(MIN(A1:D1),A1:D1,0)

Explanation: the MATCH() function returns a relative index for a cell value in a cell range, and the nested MIN() for the same range provides the value to check for.

share|improve this answer
    
+1 this works if checking each row separately. It wasn't clear to me whether a range is being looked at for the column with a min value however. –  datatoo Jul 22 '12 at 18:30
1  
nice one (and +1). I've submitted an edit because the English version of Excel uses commas , instead of semi-colons ; to separate arguments within a formula –  JMax Jul 23 '12 at 7:24
    
@JMax: ugh, yeah, thanks for catching that – I translated the formula names but forgot about the semicolons. –  kopischke Jul 23 '12 at 9:37
2  
Thanks! I used this with the index function... INDEX($G$1:$IV$1;MATCH(MIN(G2:IV2);G2:IV2;0)) I have the column names in G1 to IV1 and this returns the column name where the min value is. –  Kristinn Hrafn Þórarinsson Jul 23 '12 at 15:01
    
what if 2 columns have the same value, is it possible to shown both column names? –  Senthil A Kumar Nov 21 at 5:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.