Nested IF AND statement

[wezzy28]

Hello!

It's my first time here and I'm looking for help.

I have this formula:
=IF(and(J8<$X$8, J8>=$W$8, $Y$8, IF(AND(J8<$X$9, J8>=$W$9, $Y$9, IF(AND(J8<$X$10, J8>=$W$10, $Y$10, IF(AND(J8<$X$11, J8>=$W$11, $Y$11, IF(AND(J8<$X$12, J8>=$W$12, $Y$12, IF(AND(J8<$X$13, J8>=$W$13, $Y$13, IF(AND(J8<$X$14, J8>=$W$14,$Y$14, IF(AND(J8<$X$15, J8>=$W$15, $Y$15, IF(AND(J8<$X$16, J8>=$W$16, $Y$16, IF(AND(J8<$X$17, J8>=$W$17, $Y$17, if(and(j8>=$w$18, $Y$18,"none")))))))))))

What I'm trying to achieve is:

If cell J8's value is between 0 (X8) and 0.01(W8) then display value of cell Y8
elseif J8 is between X9 and W9 then display Y9

and it goes on until row 18

The above formula is rendering errors and wouldn't let me move forward until I've corrected the error. I tried different combinations and still nothing.

I would appreciate any assistance.

It's kind of urgent for a project which is due tomorrow.

Thank you.

 

[etaf]

you need to close the AND( bracket
=IF(and(J8<$X$8, J8>=$W$8) ,
which is why its not working
you are not going to use a IF in the middle of an AND()

I dont understand If cell J8's value is between 0 (X8) and 0.01(W8)

If cell J8's value is between 0 (X8) and 0.01(W8) then display value of cell Y8

X8 has to be at 0
W8 has to be at 0.01
Then J8 needs to be between those values

AND( X8 = 0, W8=0.01 , J8 >= 0, J8<=0.01)
is that the rule you are after ???

 

[Eric W]

Welcome to the Board.

I really dislike deeply nested formulas like that. Usually there's a much shorter way to code it using lookup functions, or possibly array formulas. Either of which would be far simpler to understand and maintain. For example, if you have a table of non-overlapping values, you can replace that whole formula with a simple VLOOKUP:

	J	K	W	X	Y
d	a
b
c
d
e
f
g
h
i
j
k

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]35[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]55[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K8[/TH]
[TD="align: left"]=VLOOKUP(J8,$W$8:$Y$18,3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If your table is different, let us know and there may still be an easier way to do it.

 

[wezzy28]

Hi Wayne!!

Thank you for the quick reply!

I closed the brackets after the 'and' - like you suggested - and it worked!!

J8 contains values (area) calculated from other cells. I hope I'm making sense?

I need to populate the Column P...

Thank you so much!!

 

[wezzy28]

Hi Eric,

Thank you for taking time to find a solution to my problem. I resolved it from the first reply. I've posted my excel sheet above and if you do find a much easier solution, I'm all ears! And grateful.

 

[Eric W]

Your tables are set up exactly the way I set up my example. You should be able to put the exact formula I posted in post # 3 in your P8 cell and drag down.