How to find which column or row the Min() value is coming from

[Guest]

My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.

 

[Alan Beban]

My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.

Assuming your data, including the column headings, begins in Cell A1 in
a range named "dataRange", you can insert the following formula in Cell
T2 and copy down:

=OFFSET($A$1,0,MATCH(MIN(INDEX(dataRange,ROW(A2),0)),INDEX(dataRange,ROW(A2),0),0)-1)

Alan Beban

 

[Bob Tarburton]

Loanne
If your headers are in a1 to s1
with data in A2:S601
t2> =index(A$1:S$1,1,match(min(A2:S2),A2:S2,0))
Copy T2 to T2:T601
Bob

 

[Guest]

This formula will retrun the cell reference

=CELL("address",INDEX($A$1:$E$20,MIN(IF($A$3:$E$20=MIN($A$3:$E$20),ROW($A$3:$E$20))),MIN(IF($A$3:$E$20=MIN($A$3:$E$20),COLUMN($A$3:$E$20)))))

entered with ctrl + shift & enter

Adapt to fit your tables ranges etc, my formula was based on that the
headers were in row2, numeric data started in 3 so note that the endex part
starts from the first row, that way you don't have to offset the results from
the min part

Regards,

Peo Sjoblom

 

[Guest]

As an example enter 1 in A1 and 2 in B1, Pen A in A2 and Pen B in B2. Let the
cells A3 to B6 contain data. In cell F3 enter
=SUMPRODUCT($A$1:$B$1,--($A3:$B3=MIN($A3:$B3)))
In cell G3 enter
=HLOOKUP(F3,$A$1:$B$4,2)
Copy cells F3 and G3 down.