# Excel, how to find column number of min value?

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 - ... - ... - ... - ... - ...
``````
-
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

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.

-
+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
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
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 '14 at 5:34