You can use VLOOKUP with last parameter as TRUE...

Set up your ranges (in ascending order) and corresponding value to get in

two adjacent columns

COL H COL I

0 0.7

49 0.9

89 1

109 1.9

300 2

and use VLOOKUP with TRUE in B1 and copy down ...

=VLOOKUP(A1,H:I,2,TRUE)

A1 should have values 0-300+

You will get the following result

0 0.7

23 0.7

49 0.9

88 0.9

89 1

108 1

109 1.9

110 1.9

298 1.9

299 1.9

300 2

330 2

"muddan madhu" wrote:

> try this

>

> =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1..6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))

>

> define the above formula, go to insert | names | define | refers to

> cell where u have the formula | name it as formula1 | ok

>

> similary define for other forumlas... then

>

> use

> =if(formula1,formula1,if(formula2,formula2,if(........................)))

>

>

>

> On Sep 30, 1:10 am, yoshi <(E-Mail Removed)> wrote:

> > Hello,

> >

> > I have 12 different values I would like to return based on a range of

> > numbers. Right now, I have it set up to display the 7 most common of them

> > through nested if statements via this formula:

> >

> > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))

> >

> > How can I include the missing values using a different function? I had read

> > to use either an index or lookup function, but don't know how to apply these.

> >

> > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90,

> > 90-109:1.00, 250-299:1.90, >=300:2.00.

> >

> > Any assistance would be highly appreciated.

> >

> > thanks!

> > Yoshi

>

>