# How do I use VLOOKUP to nest more then 7 IF statements?

Discussion in 'Microsoft Excel Misc' started by yoshi, Sep 29, 2008.

1. ### yoshiGuest

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

yoshi, Sep 29, 2008

2. ### muddan madhuGuest

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 <> 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

muddan madhu, Sep 29, 2008

3. ### SheelooGuest

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

Set up your ranges (in ascending order) and corresponding value to get in
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

> 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 <> 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

>
>

Sheeloo, Sep 29, 2008
4. ### Jim ThomlinsonGuest

Index / match tends to work better...
0 0.7
50 0.9
90 1
110 1.2
130 1.4
140 1.5
150 1.6
170 1.7
210 1.8
250 1.9
300 2

Now use the formula (In cell E1 or ???)
=INDEX(\$B\$1:\$B\$11, MATCH(D1, \$A\$1:\$A\$11, TRUE))

Where you put the amount in Cell D1
--
HTH...

Jim Thomlinson

"yoshi" 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

Jim Thomlinson, Sep 29, 2008