Find value of cheapest option

joshbjames

New Member
Joined
Jul 25, 2017
Messages
33
I have 2 columns of data. Column A is Part Number. Column B is Price

We have a record of different prices we have sold a part for and I would like a formula to id the cheapest price we have sold it for in a third column. ex.

PartNum
A124C64
A124C64
A124C64
A124C64
A124C64
A124C64
A124C64
A124C64
A124C65
A124C65
A124C65
A124C65
A124C65
A124C65
A124C65
A124C65
A124C67
A124C67
A124C67
A124C68
A124C68
A124C68
A124C6C
A124C6C
A124C6C

<colgroup><col></colgroup><tbody>
</tbody>
PastPrice
237.08
237.10
238
239
239.50
237.08
240
237.10
237.10
237.08
238
240
239.50
240
240
239
4
3
3.5
6
7
5.83
67.77
68
67.78

<colgroup><col></colgroup><tbody>
</tbody>
LowestPrice
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
237.08
3
3
3
5.83
5.83
5.83
67.77
67.77
67.77

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Try this function:
Code:
{=MIN(IF(A$2:A$26=A2,B$2:B$26))}
Note: You really do not enter the {}, that just indicates that you enter the formula using CTRL-SHIFT-ENTER instead of just ENTER.

See here for a more detailed explanation of this method: https://exceljet.net/formula/minimum-value-if
 
Upvote 0
Thank you both for your solutions. I do not have "MINIFS". The first solution: "{=MIN(IF(A$2:A$26=A2,B$2:B$26))}" worked great on my sample data, but not on the sheet. The adjusted formula was "{=MIN(IF(B$2:B$68908=B2,F$2:F$68908))}", and it would yield "0". Is there a glaring error or are there limits to the size of the range or separated columns?
 
Upvote 0
If 0 for every B value, the F values would be text numbers, not true numbers.

=ISNUMBER(F2)

should yield TRUE.
 
Upvote 0
I used this formula one time with success, but I am consistently getting "0" as the returned value. I am using ctrl+shift+enter, could there be another issue? My data is a little longer, so the formula is "=MIN(IF(A$2:A$1324=A2,B$2:B$1324))". For instance, this is my first couple lines.

PNPriceLowest
A1213DE9.230
A1213DE20.570
A1213DE9.230
A124D1529.590
A124D1530.880
A124D17142.340
A124D17142.340
A124DD6164.910
A124DD6164.910
A123F3C83.390
A123F3C72.120
A123F3D13.860
A123F3D13.860
A12454824.690
A12454822.620
A12454812.720
A12454812.720
A12454812.720
A12454819.320
A12454812.720
A12454820.570
A124EF136.030
A124EF144.280
A124EF524.150
A124EF524.150

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
It works fine for me.
It sounds like your amounts in column B are entered as Text and not Numbers (see Aladin's last message).
You can use Text to Columns to convert those all to numbers, then the formula should work.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top