Excel Newbie help finding lowest value from rows and placing results in a column

Analysis Newbie · May 8, 2010

I am an Excel newbie. I am working with data from Vendors and I am searching for the lowest costs. I have data containing thousands of rows.
I found your wonderful formula on your message boards and it works for one row of data. I need to find the same kind of result for many rows.
I first found the minimum from Columns A, B, C.
The formula I found was this one: =index(A2:C2,match(T2,A2:C2,0)).
Can I now have this formula work for the entire column? When I tried to copy it for the column I couldn't get the right result.
I was able to edit the formula for each result, but like I said, I have thousands of rows.
Thanks in advance!

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CJustin%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CJustin%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CJustin%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> <table class="MsoNormalTable" style="width: 296pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="395"> <tbody><tr style="height: 15pt;"> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> *******<o

></o

>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> Jacker<o

></o

>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> Sacker<o

></o

>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> Min Cost<o

></o

>
</td> <td style="padding: 0in 5.4pt; width: 104pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="139"> Result of Min Cost<o

></o

>
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

312<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

325<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

310<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

310<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 104pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="139"> Sacker<o

></o

>
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

72<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

69<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

70<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

69<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 104pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="139"> Jacker<o

></o

>
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

132<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

123<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

127<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

123<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 104pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="139"> Jacker<o

></o

>
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

60<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

61<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

62<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

60<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 104pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="139"> *******<o

></o

>
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

52<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

55<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

55<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">

52<o

></o

>

</td> <td style="padding: 0in 5.4pt; width: 104pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="139"> *******<o

></o

>
</td> </tr> </tbody></table>

meldoc · May 8, 2010

Hello and welcome

Try.

Excel Workbook

A

B

C

D

E

1

*******

Jacker

Sacker

Min Cost

Result of Min Cost

2

312

325

310

Sacker

3

72

69

70

69

Jacker

4

132

123

127

123

Jacker

5

60

61

62

60

*******

6

52

55

52

*******

Sheet1

Copy the formulas in columns D2 and E2, down as far as you need.

hiker95 · May 8, 2010

gillian.walker,

Welcome to the MrExcel forum.

Excel Workbook

A

B

C

D

E

1

*******

Jacker

Sacker

Min Cost

Result of Min Cost

2

312

325

310

Sacker

3

72

69

70

69

Jacker

4

132

123

127

123

Jacker

5

60

61

62

60

*******

6

52

55

52

*******

7

Sheet1

The formula in cell E2 copied down:
=INDEX($A$1:$D$1,MATCH(MIN($A2:$C2),$A2:$C2,0))

Aladin Akyurek · May 8, 2010

<TABLE style="WIDTH: 324pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=431><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2104" width=59><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1792" width=50><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1905" width=54><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2360" width=66><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2645" width=74><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 44pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 width=59>*******</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=50>Jacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Sacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=66>Min Cost</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 152pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=202 colSpan=3>Result of Min Cost</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>312</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>325</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>310</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>310</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Sacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>72</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>69</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>69</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>69</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Jacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Sacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>127</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>127</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Jacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>60</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>61</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>62</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>60</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>*******</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>*******</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Jacker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Sacker</TD></TR></TBODY></TABLE>

A1:C6 houses a sample of vendor data, with vendorrs as headers.

D2, just enter and copy down:

=MIN(A2:C2)

E2, control+shift+enter (not just enter), copy across, and the down:

Code:

=IF(COLUMNS($A2:A2)<=COUNTIF($A2:$C2,$D2),INDEX($A$1:$C$1,
    SMALL(IF($A2:$C2=$D2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1),
      COLUMNS($A2:A2))),"")

Analysis Newbie · May 9, 2010

Wow you are fast and efficient!
Thanks so much!

Excel Newbie help finding lowest value from rows and placing results in a column

Analysis Newbie

New Member

Excel Facts

meldoc

Well-known Member

hiker95

Well-known Member

Aladin Akyurek

MrExcel MVP

Analysis Newbie

New Member

Similar threads

Forum statistics

Share this page

Excel Newbie help finding lowest value from rows and placing results in a column

Analysis Newbie

New Member

Excel Facts

meldoc

Well-known Member

hiker95

Well-known Member

Aladin Akyurek

MrExcel MVP

Analysis Newbie

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock