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

Asked By yosh on 29-Sep-08 04:10 PM
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 replied on 01-Oct-08 10:15 PM
try this

=3DIF(E5>=3D250,"1.9",IF(E5>=3D210,"1.8",IF(E5>=3D170,"1.7",IF(E5>=3D150,"1=
.6",IF(E5>=3D140,"1.5",IF(E5>=3D130,"1.4",IF(E5>=3D120,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
=3Dif(formula1,formula1,if(formula2,formula2,if(........................)))



ad
ese.
0,
to_sheelo replied on 29-Sep-08 05:52 PM
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
James_Thomlinso replied on 29-Sep-08 05:55 PM
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