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