Returning the lowest value from a range

[delamerespartan]

Hi, apologies in advance if this question is very basic. I'm using Excel 2010 and want to lookup a range of data based on a set criteria (Item number or description) and return the lowest unit rate for the item (5th Column). I have a huge dataset (thousands of rows) and want a formula to copy down the column for speed.

[TABLE="width: 453"]
<TBODY>[TR]
[TD]Item number</SPAN>[/TD]
[TD]Item name</SPAN>[/TD]
[TD]Invoiced quantity - basic unit</SPAN>[/TD]
[TD]Total Order value £</SPAN>[/TD]
[TD]unit rate</SPAN>[/TD]
[TD]Lowest Rate</SPAN>[/TD]
[/TR]
[TR]
[TD]1000000123</SPAN>[/TD]
[TD]Widget 1</SPAN>[/TD]
[TD]34</SPAN>[/TD]
[TD]£180.00</SPAN>[/TD]
[TD]5.29</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000124</SPAN>[/TD]
[TD]Widget 2</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]£38.00</SPAN>[/TD]
[TD]12.67</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000124</SPAN>[/TD]
[TD]Widget 2</SPAN>[/TD]
[TD]6</SPAN>[/TD]
[TD]£85.00</SPAN>[/TD]
[TD]14.17</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000124</SPAN>[/TD]
[TD]Widget 2</SPAN>[/TD]
[TD]25</SPAN>[/TD]
[TD]£330.00</SPAN>[/TD]
[TD]13.20</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000124</SPAN>[/TD]
[TD]Widget 2</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]£40.00</SPAN>[/TD]
[TD]13.33</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000124</SPAN>[/TD]
[TD]Widget 2</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]£13.00</SPAN>[/TD]
[TD]13.00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000124</SPAN>[/TD]
[TD]Widget 2</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]£53.00</SPAN>[/TD]
[TD]13.25</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]£16.00</SPAN>[/TD]
[TD]16.00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]7</SPAN>[/TD]
[TD]£112.00</SPAN>[/TD]
[TD]16.00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]53</SPAN>[/TD]
[TD]£851.00</SPAN>[/TD]
[TD]16.06</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]£48.00</SPAN>[/TD]
[TD]16.00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]£31.00</SPAN>[/TD]
[TD]15.50</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]£16.00</SPAN>[/TD]
[TD]16.00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]5</SPAN>[/TD]
[TD]£80.00</SPAN>[/TD]
[TD]16.00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000000125</SPAN>[/TD]
[TD]Widget 3</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]£158.00</SPAN>[/TD]
[TD]15.80</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2></COLGROUP>[/TABLE]

 

[jduley]

does =MIN(E:E) give you want you need?

 

[shg]

Like this?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Item number​

[/td][td]

Item name​

[/td][td]

Invoiced quantity - basic unit​

[/td][td]

Total Order value £​

[/td][td]

unit rate​

[/td][td]

Lowest Rate​

[/td][/tr]

[tr][td]

2​

[/td][td]

1000000123​

[/td][td]Widget 1[/td][td]

34​

[/td][td]

$ 180​

[/td][td]

$ 5.29​

[/td][td]

$ 5.29​

[/td][/tr]

[tr][td]

3​

[/td][td]

1000000124​

[/td][td]Widget 2[/td][td]

3​

[/td][td]

$ 38​

[/td][td]

$ 12.67​

[/td][td]

$ 12.67​

[/td][/tr]

[tr][td]

4​

[/td][td]

1000000124​

[/td][td]Widget 2[/td][td]

6​

[/td][td]

$ 85​

[/td][td]

$ 14.17​

[/td][td]

$ 12.67​

[/td][/tr]

[tr][td]

5​

[/td][td]

1000000124​

[/td][td]Widget 2[/td][td]

25​

[/td][td]

$ 330​

[/td][td]

$ 13.20​

[/td][td]

$ 12.67​

[/td][/tr]

[tr][td]

6​

[/td][td]

1000000124​

[/td][td]Widget 2[/td][td]

3​

[/td][td]

$ 40​

[/td][td]

$ 13.33​

[/td][td]

$ 12.67​

[/td][/tr]

[tr][td]

7​

[/td][td]

1000000124​

[/td][td]Widget 2[/td][td]

1​

[/td][td]

$ 13​

[/td][td]

$ 13.00​

[/td][td]

$ 12.67​

[/td][/tr]

[tr][td]

8​

[/td][td]

1000000124​

[/td][td]Widget 2[/td][td]

4​

[/td][td]

$ 53​

[/td][td]

$ 13.25​

[/td][td]

$ 12.67​

[/td][/tr]

[tr][td]

9​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

1​

[/td][td]

$ 16​

[/td][td]

$ 16.00​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

10​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

7​

[/td][td]

$ 112​

[/td][td]

$ 16.00​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

11​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

53​

[/td][td]

$ 851​

[/td][td]

$ 16.06​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

12​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

3​

[/td][td]

$ 48​

[/td][td]

$ 16.00​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

13​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

2​

[/td][td]

$ 31​

[/td][td]

$ 15.50​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

14​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

1​

[/td][td]

$ 16​

[/td][td]

$ 16.00​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

15​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

5​

[/td][td]

$ 80​

[/td][td]

$ 16.00​

[/td][td]

$ 15.50​

[/td][/tr]

[tr][td]

16​

[/td][td]

1000000125​

[/td][td]Widget 3[/td][td]

10​

[/td][td]

$ 158​

[/td][td]

$ 15.80​

[/td][td]

$ 15.50​

[/td][/tr]
[/table]

The formula in F2, which MUST be confirmed with Ctrl+Shift+Enter instead of just Enter, is

=MIN(IF($B$2:$B$16 = B2, $E$2:$E$16))

 

[delamerespartan]

Thanks shg, exactly like that! Worked a treat! Cheers!

 

[shg]

You're welcome.