Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 10 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
4  
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
1  
what if 2 columns have the same value, is it possible to shown both column names? – Senthil A Kumar Nov 21 '14 at 5:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .