Vlookup with MIN Function

[shonu1320]

The following formula I have is to find smallest value from column M, N,O and P for the Item in A5.
=IF(ISBLANK(A5),"",IF(ISERROR(SMALL(M5:P5,COUNTIF(M5:P5,0)+1)),"NOT FOUND",SMALL(M5:P5,COUNTIF(M5:P5,0)+1)))

Can it be done with Vlookup and MIN function instead of SMALL and COUNTIF.

For example I have my Data Table, If I do a Vlookup with value in A5, Table array being my data table in which I will select those 4 columns(M,N,O,P) and then what for the column index number? and can I just add MIN in front of Vlookup? Confused!
I am really new at these formulas and trying hard with excel help and this forum to learn EXCEL. Please advice!

 

[MrKowz]

Can you please provide some sample data as well as desired outcome?

 

[shonu1320]

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=20 width=64>A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>C</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>E</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Product</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Price 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Price 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Price 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Price 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>MIN Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Apron</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=xl65 align=right>42</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=xl65 align=right>0</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=xl65 align=right>0</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=xl65 align=right>40.58</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=xl65 align=right>40.58</TD></TR></TBODY></TABLE>

So I have a product, and I have 4 prices for it. In my F Column I want it to show me the minimum of those 4 prices when I do Vlookup.(I am doing vlookup because this formula will be shown in different spreadsheet than this.) Does this makes sense? I must do a vlookup with MIN function.

 

[MrKowz]

Is this what you're looking to do?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Product</td><td style="font-weight: bold;;">Price 1</td><td style="font-weight: bold;;">Price 2</td><td style="font-weight: bold;;">Price 3</td><td style="font-weight: bold;;">Price 4</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Product</td><td style="font-weight: bold;;">Pot</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Apron</td><td style="text-align: right;;">42</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">40.58</td><td style="text-align: right;;"></td><td style=";">Min Price</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Pan</td><td style="text-align: right;;">30</td><td style="text-align: right;;">32</td><td style="text-align: right;;">31.4</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Pot</td><td style="text-align: right;;">0</td><td style="text-align: right;;">42</td><td style="text-align: right;;">40</td><td style="text-align: right;;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Spatula</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;">14.5</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Tongs</td><td style="text-align: right;;">13.2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">{=MIN(<font color="Blue">INDEX(<font color="Red">IF(<font color="Green">B2:E6<>0,B2:E6,""</font>),MATCH(<font color="Green">H1,A2:A6,0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[shonu1320]

I am looking for a minimum price for each pof those products. So for Apron, Min price will be 40.58, for Pan, it will be 30, and for Pot, it will be 40.

 

[MrKowz]

I really don't understand what you are trying to accomplish. The formula I provided will look up whatever is in H2 and return the minimum price for that value.

Are you just simply wanting to make a column for that data that gives you the minimum price? If so, no vlookup or index/match is needed:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Product</td><td style="font-weight: bold;;">Price 1</td><td style="font-weight: bold;;">Price 2</td><td style="font-weight: bold;;">Price 3</td><td style="font-weight: bold;;">Price 4</td><td style="font-weight: bold;;">Min price</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Apron</td><td style="text-align: right;;">42</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">40.58</td><td style="text-align: right;;">40.58</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Pan</td><td style="text-align: right;;">30</td><td style="text-align: right;;">32</td><td style="text-align: right;;">31.4</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Pot</td><td style="text-align: right;;">0</td><td style="text-align: right;;">42</td><td style="text-align: right;;">40</td><td style="text-align: right;;">41</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Spatula</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;">14.5</td><td style="text-align: right;;">15</td><td style="text-align: right;;">14.5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Tongs</td><td style="text-align: right;;">13.2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">16</td><td style="text-align: right;;">13.2</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">B2:E2<>0,B2:E2,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[shonu1320]

Thats exactly what I am needing but I thought I will have to do a Vlookup also. I guess not. Thank you so much for your help!