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 - ... - ... - ... - ... - ...1 Answer
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.
