more than 8 if's are not working in a cell

[sathyguy]

Friends,

I am new to this forum....nice to meet you all...
Below is working fine....

=IF(D4=5,31,IF(D4=10,56,IF(D4=15,83,IF(D4=20,110,IF(D4=25,135,IF(D4=50,268,IF(D4=100,530,IF(D4=200,1050))))))))

but if i add another if like below

then i am getting an error as "the formula containing an error

=IF(D4=5,31,IF(D4=10,56,IF(D4=15,83,IF(D4=20,110,IF(D4=25,135,IF(D4=50,268,IF(D4=100,530,IF(D4=200,1050,IF(D4=200,1560)))))))))

where i am making mistake?

thanks

[ajaykgarg]

You are not making any mistake. The IF function is limited to 8 nestings by Excel.

hth
Ajay

[sathyguy]

oh.thanks.....so what is the solution for another 3 if's...?

[ajaykgarg]

I think a lookup should work for you.

hth
Ajay

[squiggler47]

you could do this :-

=IF(D4>200,1560,IF(ISERROR(INDEX({31,56,83,110,135,268,530,1050},1,MATCH(D4,{5,10,15,20,25,50,100,200},0))),-1,INDEX({31,56,83,110,135,268,530,1050},1,MATCH(D4,{5,10,15,20,25,50,100,200},0))))

I assumed that your formula would return 1560 if the value is above 200

The red is the values you want in the cell, and the blue is the value that is in cell d4, you can add as many terms as you want!

it would be better to put the values in a cell and refer to it than hard coding in the formula!

it returns -1 if the value is not found

[sathyguy]

I am doing this in the column "P" ... can you help me how to achieve this?
actually, when i type value in column "D", then the value equal to the column "D" should be displayed in column "P"...
example.
if i type 5 in D4 then in P4 the value should be 31

[canapone]

Hi, a vlookup like the following could give you more room to move.

Sorry if I did not put the right correspondences (in the example 5 becomes 31 20 becomes 110 ...)

=VLOOKUP(D4,{5,31\20,110\25,135\50,268\100,530\200,1050},2,false)

You can adjust the false/true option or you can nest the formula into a shorter IF .

=IF(D4<5;....;FORMULA)

Hope it helps

[squiggler47]

I am doing this in the column "P" ... can you help me how to achieve this?
actually, when i type value in column "D", then the value equal to the column "D" should be displayed in column "P"...
example.
if i type 5 in D4 then in P4 the value should be 31

just put my formula in P4 then copy down!

[zbor]

You didn't write what if D4 is 200?
You have two same answers.....
This one maybe?

=LOOKUP(D4,{0,5,10,15,20,25,50,100,200},{0,31,56,83,110,135,268,530,1050})

[sathyguy]

@canapone....
its giving error...

[sathyguy]

@squiggler47
this is what....i want...

=IF(D4=5,31,IF(D4=10,56,IF(D4=15,83,IF(D4=20,110,IF(D4=25,135,IF(D4=50,268,IF(D4=100,530,IF(D4=200,1050,IF(D4=300,1560,IF(D4=400,2050,IF(D4=500,2550)))))))))))

[excelmatic]

sathyguy,

you should use Choose() function to achieve what you wanted to achieve with nested IF

Excel Matic

[squiggler47]

=IF(D4>200,1560,IF(ISERROR(INDEX({31,56,83,110,135,268,530,1050},1,MATCH(D4,{5,10,15,20,25,50,100,200},0))),FALSE,INDEX({31,56,83,110,135,268,530,1050},1,MATCH(D4,{5,10,15,20,25,50,100,200},0))))

again Lookup is providing different results to the OP!

I changed the -1 to FALSE, so any value not in the list will produce FALSE

[sathyguy]

@squiggler47
Still if i enter any not listed numbers its not showing as false.
its showing the previous value.

[squiggler47]

@squiggler47
Still if i enter any not listed numbers its not showing as false.
its showing the previous value.

the last formula sent by me, behaves exactly like yours!

make sure you add the numbers to both sets!

[sathyguy]

thanks...below one is working fine...
=IF(D4>500,2550,IF(ISERROR(INDEX({31,56,83,110,135,268,530,1050,1560,2060,2550},1,MATCH(D4,{5,10,15,20,25,50,100,200,300,400,500},0))),FALSE,INDEX({31,56,83,110,135,268,530,1050,1560,2060,2550},1,MATCH(D4,{5,10,15,20,25,50,100,200,300,400,500},0))))

but the problem is ....
if i enter 501 or above then its not showing false.

[squiggler47]

=if(d4>500,false,if(iserror(index({31,56,83,110,135,268,530,1050,1560,2060,2550},1,match(d4,{5,10,15, 20,25,50,100,200,300,400,500},0))),false,index({31,56,83,110,135,268,530,1050,1560,2060,2550},1,matc h(d4,{5,10,15,20,25,50,100,200,300,400,500},0))))

[daddylonglegs]

The simplest way, I think, is to create a lookup table,

For example list the possible values for D4 in Y2:Y12 and the corresponding values you want to return in Z2:Z12, e.g. Y2 = 5, Z2 =31, Y3 = 10, Z3 = 56 etc. then you can use this formula

=VLOOKUP(D4,$Y$2:$Z$12,2,0)

or if you really don't want a lookup table then you can put those values in the formula, i.e.

=VLOOKUP(D4,{5,31;10,56;15,83;20,110;25,135;50,268;100,530;200,1050;300,1560;400,2050;500,2550},2,0)

If D4 isn't any of those values you'll get #N/A

[squiggler47]

or even :-

=if(iserror(index({31,56,83,110,135,268,530,1050,1560,2060,2550},1,match(d4,{5,10,15 , 20,25,50,100,200,300,400,500},0))),false,index({31,56,83,110,135,268,530,1050,1560,2060,2550},1,match(d4,{5,10,15,20,25,50,100,200,300,400,500},0)))

I had made an assumption on your original formula, this will return false if it is not in the list!

[friendlykashish]

Use COUNTIF formula, sure it will work

[sathyguy]

@squiggler47 ------ you are great.....

this is what i want.......thank you so much...

[daddylonglegs]

If you use a table and really want FALSE as a result when D4 is none of those values you can amend the VLOOKUP as follows

=IF(COUNTIF($Y$2:$Y$12,D4),VLOOKUP(D4,$Y$2:$Z$12,2,0))

[Miraun]

REALLY overcomplicated the answer...

I've had many problems with imbedded if statement.

Here's what you originally had:
=IF(D4=5,31,IF(D4=10,56,IF(D4=15,83,IF(D4=20,110,IF(D4=25,135,IF(D4=50,268,IF(D4=100,530,IF(D4=200,1 050))))))))

Here's the proposed change:
=If(D4=5,31,0)+If(D4=10,56,0)+... etc...etc
Then you're just adding up 0s unless it's the value you're looking for.