Help with Excel Lookup and next highest number

[GJParker]

Hi folks

I have an Excel lookup table that looks something like this

Quantity    	Cost/000
10,000	        30.15
25,000	        27.50
50,000	        26.66
100,000	        26.25
250,000	        24.54
500,000	        24.01
750,000	        23.82
1,000,000       23.71

What i need to do is, when I enter a value if an exact match is not found, the next smallest value that is greater than the lookup_value is returned.

e.g. If I enter a lookup value of 15,000 then the value for 25,000 (27.50) should be returned.

the exception to this rule is values above 1,000,000 should return the lookup value for 1,000,000

Thanks in advance





Gary Parker
MIS Data Analyst
Manchester, England

 

[GJParker]

It's okay folks I woke up and the mist cleared, simply change the lookup table values to

Quantity        Cost/000
0               30.15
10,001          27.50
25,001          26.66
50,001          26.25
100,001         24.54
250,001         24.01
500,001         23.82
750,001         23.71

Gary Parker
MIS Data Analyst
Manchester, England