Returning value based on input data

[mtroute]

I am trying to write a formula or VBscript that will accomplish this seemingly simple task:

In CELL A I want to enter a value, in this case the price of diesel fuel per gallon. I then want to compare that value against a table that contains 3 columns ABC and return a value base on that comparison, for example:

Current Diesel Price = x

if x is greater than w but less than y return value z. and if thats false check the next row in the table until true.

the lookup table looks like this

greater than less than charge %
1.00 1.05 14%


I need a single line for entry and a single result line, such as:

(user enters this value)
D.O.E. PRICE: 2.654
(formula returns this value)
FSC% 16.50%


I need it to look up the value from a table on another sheet formated as:

DOE Price

at least less than FSC%
2.31 2.32 14.0
2.32 2.33 14.1
2.33 2.34 14.2
2.34 2.35 14.3
2.35 2.36 14.4
2.36 2.37 14.5
2.37 2.38 14.6


Thanks for any help.

[Mark Lincoln]

If you can create a helper column on your sheet with the lookup data,
you can do this:

Assuming entry in Sheet1, A1 and result in Sheet2, A2, with lookup data
in Sheet2 columns A-C and the helper column in D.

In Sheet2, column D enter this next to your first FSC% figure:

=IF(AND(Sheet1!$A$1>=Sheet2!A1,Sheet1!$A$1<Sheet2!B1),Sheet2!C1,0)

Drag that formula down to your last data row.

in Sheet1, A2 enter:

=MAX(Sheet2!D1:D10)

substituting your helper column range for D1:D10.

When you enter a value in A1, the helper column puts zeros in each of
its rows save the one meeting your criteria. The formula in A2 simply
finds the only nonzero value in the range.

[Mark Lincoln]

An addendum: The formula below assumes your first data row is row 1.
If different, the references to Sheet2!A1, Sheet2!B1 and Sheet2!C1
would change (the 1 would change to whichever row number your formula
is being entered into).

=IF(AND(Sheet1!$A$1>=Sheet2!A1,Sheet1!$A$1<Sheet2!B1),Sheet2!C1,0)