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

yoshi
Guest
Posts: n/a

 29th Sep 2008
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

Guest
Posts: n/a

 29th Sep 2008
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

Sheeloo
Guest
Posts: n/a

 29th Sep 2008
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 <(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

>
>

Jim Thomlinson
Guest
Posts: n/a

 29th Sep 2008
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post KenCanuck Microsoft Excel Misc 13 9th Mar 2010 09:31 AM =?Utf-8?B?TGFkeUhhd2s=?= Microsoft Excel Worksheet Functions 9 11th May 2007 06:45 PM =?Utf-8?B?Ti5SLg==?= Microsoft Excel Worksheet Functions 2 23rd Jun 2005 06:14 PM Ralph Malph Windows XP General 2 9th Feb 2005 07:05 AM =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM

Features