Hey Rob

Assume Recommended Tank Size is in A2
and Range of Tank Sizes Available is in A1 to J1
In A3 put
=MIN(IF(A1:J1A2,A1:J1))
This in array formula, so commit with Ctrl+Shift+Enter
which will place {} brackets around the formula.
This will give the minimum value greater than recommended regardless
of the order the available tanks are entered

You won't be able to use this as part of a larger formula, so you will
have to A3 if further calculation is needed

Bob


On Wed, 25 May 2005 07:29:40 -0500, TheRobsterUK
wrote:

Hi,

I am trying to get Excel to look up a certain value from a small table
of values and have tried using the VLOOKUP/HLOOKUP functions but can't
get them to do exactly what I want. Here is how the spreadsheet looks:

Recommended Tank Size: 4400 litres

Range of Tank Sizes Available (litres):
1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000

Basically their is an algorithm which calculates the "Recommended Tank
Size" and then Excel is supposed to look up the nearest but next
-largest- value from the "Range of Tank Sizes Available" table.

The problem I am having is that the VLOOKUP/HLOOKUP function only
returns the next -smallest- value. This seems to be a hard coded
feature and I can't change it. I also tried listing the tank sizes in
reverse (descending) order but this just produces and error.

Can anyone think of a way to get Excel to look up the next largest
figure from the data table rather than the nearest smallest?

Cheers
-Rob