Sorry, these seems to have got too complicated than I needed.

Basically I want to enter a result when a cell is between two values.

ie.

Enter "Value" when A1 is more than 0 but less than 2 or enter "another
Value" when A1 is more than 2 but less than 3. And so on. I can live with
having the result "value" being 0 if not in one of the ranges.

The "value" doesn't have to be a numerical one.

Is this possible?

Thanks

"Arvi Laanemets" wrote:

Hi

There are several options.

1. There exist a certain pattern in both source and result values. P.e.
limit values are 2.50, 3.50, 4.50, ...etc. and according return values are
3.99, 4.99, 5.50, ... etc., then this formula will do:
=3.99+INT(MAX(0,A1-1.5000000001))


2. There isn't any pattern, or the formula will be too complex, and the
number of values to check is too high, then use lookup table and VLOOKUP
function (only way when you have more than 29 values to check):

On separate sheet, enter the lookup table
LowerLimit Response
-9999999999 0
0.0100000001 3.99
2.50500000001 4.99
....

, and then use the formula:
=VLOOKUP(A1,LookupTable,2,1)


3. When number of lookup values isn't very big, you can use same VLOOKUP
formula with lookup values entered directly as an array (but only, when your
regional settings allow this - p.e. I myself can't use this as my regional
settings have ; as parameter delimiter)
=VLOOKUP(A1,{-9999999999;0,0.0100000001;3.99,2.5000000001;4.99,. ...},2,1)

4. When the number of lookup values doesn't exceed 29, you can combine
CHOOSE and MATCH functions:
=CHOOSE(MATCH(A1,{-99999;0.01000001;2.5000001;...},1),0,3.99,4.99,... )

5. And at least, when the number of lookup values doesn't exceed 8, you can
use IF function:
=IF(A1<=0.01,0,IF(A1<=2.5,3.99,IF(...,...,...)))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Tigerwould" wrote in message
...
I'm trying to return multiple results using IF function for the following.

If A1 is greater that 0.01 and less than 2.50 then enter 3.99, but if A1
is
greater than 2.50 and less than 3.50 then enter 4.99.

I can do it for one:

=IF(AND(J22.50,J2<3.50),"4.99")

But not multiples.

Help!