vlookup with multiple results

[chad13]

Column A is a list of part numbers and column B is a list of the associated unit prices. The part number can show up multiple times with various unit prices associated with them.

Is there a way to lookup a specific part number and the result to show the lowest unit price or even the largest largetst price?

Any help would greatly be appreciated.

Chad

 

[Haseeb Avarakkan]

Try;

C2 = Part Number,

For the Smallest Price;

=MIN(IF(A2:A100=C2,B2:B100))

For the Largest Price;

=MAX(IF(A2:A100=C2,B2:B100))

 

[chad13]

This only returns the min/max of the entire range, not the min/max for the specific part number. Keep in mind that there are multiple part numbers and they can show up various times.

 

[chad13]

Here is a brief example:

Part Number Unit $
Part 1 $79.00
Part 2 $81.00
Part 3 $95.00
Part 4 $85.00
Part 5 $76.00
Part 6 $57.00
Part 7 $90.00
Part 8 $27.00
Part 9 $32.00
Part 10 $18.00
Part 11 $28.00
Part 1 $34.00
Part 2 $5.00
Part 3 $24.00
Part 4 $74.00
Part 5 $23.00
Part 6 $79.00
Part 7 $12.00
Part 8 $66.00
Part 9 $59.00
Part 10 $46.00
Part 11 $89.00

I would like a lookup, for example, to show the min value for "part 1" or the max for "part 1". The min would be $34 and the max would be $79.

Thanks.

 

[Haseeb Avarakkan]

You need to press CTRL+SHIFT+ENTER, not just ENTER.

<b>Excel 2007</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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Part Number</td><td style="font-weight: bold;;">Unit $</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;">2</td><td style=";">Part 1</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">lookup value</td><td style="font-weight: bold;;">Part 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Part 2</td><td style="text-align: right;;">81</td><td style="text-align: right;;"></td><td style=";">MIN</td><td style="text-align: right;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Part 3</td><td style="text-align: right;;">95</td><td style="text-align: right;;"></td><td style=";">MAX</td><td style="text-align: right;;">79</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Part 11</td><td style="text-align: right;;">28</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;">13</td><td style=";">Part 1</td><td style="text-align: right;;">34</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;">23</td><td style=";">Part 11</td><td style="text-align: right;;">89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><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">E3</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">A2:A23=E2,B2:B23</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">A2:A23=E2,B2:B23</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 />

 

[chad13]

Perfect.
Thank you very much.

 

[chad13]

Now, one step further.
This formula works great.
Is there a way to have it pick up the lowest that is not "0"?

 

[Haseeb Avarakkan]

Try;

=MIN(IF(A2:A23=E2,IF(B2:B23<>0,B2:B23)))

Confirmed with CTRL+SHIFT+ENTER.

 

[chad13]

That's it.
Thank you.