PC Review


Reply
Thread Tools Rate Thread

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
 
Reply With Quote
 
 
 
 
muddan madhu
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


 
Reply With Quote
 
 
 
 
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
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

>
>

 
Reply With Quote
 
Jim Thomlinson
Guest
Posts: n/a
 
      29th Sep 2008
Index / match tends to work better...
In A1:B11 add
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I nest more then 7 IF's? KenCanuck Microsoft Excel Misc 13 9th Mar 2010 09:31 AM
Nest more then seven if conditions =?Utf-8?B?TGFkeUhhd2s=?= Microsoft Excel Worksheet Functions 9 11th May 2007 06:45 PM
if greater then 99 then 1 if greater then 199 then two =?Utf-8?B?Ti5SLg==?= Microsoft Excel Worksheet Functions 2 23rd Jun 2005 06:14 PM
Need help with a log off/log on problem...can log off then log on dif. user and system will be slow loading user settings, then play start up music, then show wallpaper, then freeze. Ralph Malph Windows XP General 2 9th Feb 2005 07:05 AM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:13 AM.