Find value of cheapest option

[joshbjames]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 62"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PartNum[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C64[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C65[/TD]
[/TR]
[TR]
[TD]A124C67[/TD]
[/TR]
[TR]
[TD]A124C67[/TD]
[/TR]
[TR]
[TD]A124C67[/TD]
[/TR]
[TR]
[TD]A124C68[/TD]
[/TR]
[TR]
[TD]A124C68[/TD]
[/TR]
[TR]
[TD]A124C68[/TD]
[/TR]
[TR]
[TD]A124C6C[/TD]
[/TR]
[TR]
[TD]A124C6C[/TD]
[/TR]
[TR]
[TD]A124C6C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PastPrice[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.10[/TD]
[/TR]
[TR]
[TD]238[/TD]
[/TR]
[TR]
[TD]239[/TD]
[/TR]
[TR]
[TD]239.50[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]240[/TD]
[/TR]
[TR]
[TD]237.10[/TD]
[/TR]
[TR]
[TD]237.10[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]238[/TD]
[/TR]
[TR]
[TD]240[/TD]
[/TR]
[TR]
[TD]239.50[/TD]
[/TR]
[TR]
[TD]240[/TD]
[/TR]
[TR]
[TD]240[/TD]
[/TR]
[TR]
[TD]239[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]5.83[/TD]
[/TR]
[TR]
[TD]67.77[/TD]
[/TR]
[TR]
[TD]68[/TD]
[/TR]
[TR]
[TD]67.78[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<colgroup><col></colgroup><tbody>[TR]
[TD]LowestPrice[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]237.08[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]5.83[/TD]
[/TR]
[TR]
[TD]5.83[/TD]
[/TR]
[TR]
[TD]5.83[/TD]
[/TR]
[TR]
[TD]67.77[/TD]
[/TR]
[TR]
[TD]67.77[/TD]
[/TR]
[TR]
[TD]67.77[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Try this function:

{=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

 

[Aladin Akyurek]

If MINIFS is available on your system, just enter in C2 and copy down...

=MINIFS(B$2:B$26,A$2:A$26,A2)

 

[joshbjames]

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?

 

[Aladin Akyurek]

If 0 for every B value, the F values would be text numbers, not true numbers.

=ISNUMBER(F2)

should yield TRUE.

 

[joshbjames]

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.

[TABLE="width: 203"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PN[/TD]
[TD]Price[/TD]
[TD]Lowest[/TD]
[/TR]
[TR]
[TD]A1213DE[/TD]
[TD]9.23[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A1213DE[/TD]
[TD]20.57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A1213DE[/TD]
[TD]9.23[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124D15[/TD]
[TD]29.59[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124D15[/TD]
[TD]30.88[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124D17[/TD]
[TD]142.34[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124D17[/TD]
[TD]142.34[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124DD6[/TD]
[TD]164.91[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124DD6[/TD]
[TD]164.91[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A123F3C[/TD]
[TD]83.39[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A123F3C[/TD]
[TD]72.12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A123F3D[/TD]
[TD]13.86[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A123F3D[/TD]
[TD]13.86[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]24.69[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]22.62[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]12.72[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]12.72[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]12.72[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]19.32[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]12.72[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124548[/TD]
[TD]20.57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124EF1[/TD]
[TD]36.03[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124EF1[/TD]
[TD]44.28[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124EF5[/TD]
[TD]24.15[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A124EF5[/TD]
[TD]24.15[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

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.