mins values

[mrjones0305]

I was wondering if there was a way for excel to find the minimum value in a row and return the column header for that value.

 

[schielrn]

Welcome to the Board!

Maybe something like:

=index(A1:G1,1,match(min(A2:G2),A2:G2,0))

You didn't give us any data or any clue as far as what to point this to, so this is just an example.

If there are 2 minimum values, it will only return the first one.

Hope that helps.

 

[VoG]

Hello and welcome to MrExcel.

For row 4 and headers in row 1 try

=INDEX(1:1,MATCH(MIN(4:4),4:4,0))

 

[mrjones0305]

I'm sorry - new to this

I was looking for something like:

(A1) Las Vegas - (A2) 123
(B1) Cleveland - (B2) 150
(C1)West Virgina - (C2) 100

I would want it to give me the result of West Virginia. What happens if it has two columns that are both the lowest?

 

[VoG]

If two columns have the same number it will return the leftmost match:

<b>Sheet15</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:108px;" /><col style="width:108px;" /><col style="width:108px;" /><col style="width:108px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Las Vegas</td><td >Cleveland</td><td >West Virginia</td><td >Akron</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">123</td><td style="text-align:right; ">150</td><td style="text-align:right; ">100</td><td style="text-align:right; ">100</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >West Virginia</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A7</td><td >=INDEX(1:1,MATCH<span style=' color:008000; '>(MIN<span style=' color:#0000ff; '>(4:4)</span>,4:4,0)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[mrjones0305]

Okay - got that to work now. Is there a way if West Virginia and Las Vegas were the same value to have excel pull them both?

 

[VoG]

Okay - got that to work now. Is there a way if West Virginia and Las Vegas were the same value to have excel pull them both?

Sorry, I don't know how to do that with a formula. Here's a UDF.

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in

Function MatchMin(r As Integer) As String
Application.Volatile
Dim LC As Integer, vmin As Variant, i As Integer
LC = Cells(r, Columns.Count).End(xlToLeft).Column
vmin = WorksheetFunction.Min(Range(Cells(r, 1), Cells(r, LC)))
For i = 1 To LC
    If Cells(r, i).Value = vmin Then MatchMin = MatchMin & Cells(1, i).Value & ", "
Next i
MatchMin = Left(MatchMin, Len(MatchMin) - 2)
End Function

Press ALT + F11 to return to your sheet. To use the UDF for example to look at row 4 enter the formula

=matchmin(4)

Example:

<b>Sheet15</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:155px;" /><col style="width:108px;" /><col style="width:108px;" /><col style="width:108px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Las Vegas</td><td >Cleveland</td><td >West Virginia</td><td >Akron</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">123</td><td style="text-align:right; ">150</td><td style="text-align:right; ">100</td><td style="text-align:right; ">100</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >West Virginia, Akron</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A9</td><td >=matchmin(4)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4