IF AND help

[ddub25]

=IF(J18<$A$5,$D$4,IF(AND(J18>=$A$5,J18<$A$7),$D$6,
IF(AND(J18>=$A$7,J18<$A$9),$D$8,IF(AND(J18>=$A$9,J18<$A$11),$D$10,
IF(AND(J18>=$A$11,J18<$A$13),$D$12,IF(AND(J18>=$A$13,J18<!$A$15),$D$14,
IF(AND(J18>=$A$15,J18<$A$17),$D$16,IF(AND(J18>=$A$17,J18<$A$19),$D$18,
IF(AND(J18>=$A$19,J18<$A$21),$D$20,IF(AND(J18>=$A$21,J18<$A$23),$D$22))))))))))

I want to replace the first IF of this formula:
=IF(J18<$A$5,$D$4,

with,
IF(AND(J18>=$A$3,J18<$A$5),$D$4,

but when I do I seem to get a formula error. I also want "--" to be returned if none of the conditions are met. Can anyone help with the syntax? Thanks.

Dan

 

[shg]

Maybe ...

=IF(J18<$A$3, "--",
 IF(J18<$A$5,  $D$4,
 IF(J18<$A$7,  $D$6,
 IF(J18<$A$9,  $D$8,
 IF(J18<$A$11, $D$10,
 IF(J18<$A$13, $D$12,
 IF(J18<$A$15, $D$14,
 IF(J18<$A$17, $D$16,
 IF(J18<$A$19, $D$18,
 IF(J18<$A$21, $D$20,
 IF(J18<$A$23, $D$22, "--")))))))))))

 

[Jonmo1]

Try

=IF(AND(J18>=$A$3,J18<$A$5),$D$4,IF(AND(J18>=$A$5,J18<$A$7),$D$6,IF(AND(J18>=$A$7,J18<$A$9),$D$8,IF(AND(J18>=$A$9,J18<$A$11),$D$10,IF(AND(J18>=$A$11,J18<$A$13),$D$12,IF(AND(J18>=$A$13,J18<$A$15),$D$16, IF(AND(J18>=$A$15,J18<$A$17),$D$18,IF(AND(J18>=$A$17,J18<$A$19),$D$20,IF(AND(J18>=$A$19,J18<$A$21),$D$22,IF(AND(J18>=$A$21,J18<$A$23),$D$24,"--"))))))))))


However, as a point of interest...
What is in the intermediate cells in column A (A4, A6, A8, A10 etc..)
If those are non numeric, a simple lookup could replace that entire formula..

=LOOKUP(J18,$A$3:$A$23,$D$4:$D$24)

And to return "--" when none of criteria are met..

=IFERROR(LOOKUP(J18,$A$3:$A$23,$D$4:$D$24),"--")

 

[jasonb75]

However, as a point of interest...
What is in the intermediate cells in column A (A4, A6, A8, A10 etc..)
If those are non numeric, a simple lookup could replace that entire formula..

=LOOKUP(J18,$A$3:$A$23,$D$4:$D$24)

And to return "--" when none of criteria are met..

=IFERROR(LOOKUP(J18,$A$3:$A$23,$D$4:$D$24),"--")

If the intermediate cells don't permit the use of jonmo's suggestion then

=IFERROR(SUBSTITUTE(INDEX($D$2:$D$22,MIN(IF(MOD(ROW($A$3:$A$23),2),IF(J18<$A$3:$A$23,ROW($A$3:$A$23))))-2),D2,"--"),"--")

Array confirmed with Shift Ctrl Enter should work.

 

[ddub25]

Thanks for all your help. I used:

=IF(AND(J18>=$A$3,J18<$A$5),$D$4,IF(AND(J18>=$A$5,J18<$A$7),$D$6,IF(AND(J18>=$A$7,J18<$A$9),$D$8,IF(AND(J18>=$A$9,J18<$A$11),$D$10,IF(AND(J18>=$A$11,J18<$A$13),$D$12,IF(AND(J18>=$A$13,J18<$A$15),$D$16, IF(AND(J18>=$A$15,J18<$A$17),$D$18,IF(AND(J18>=$A$17,J18<$A$19),$D$20,IF(AND(J18>=$A$19,J18<$A$21),$D$22,IF(AND(J18>=$A$21,J18<$A$23),$D$24,"--"))))))))))

by jonmo. The intermediate cells in column A (A4, A6, A8, A10 etc..) are all empty, but the cells that are being referenced are all numeric. Thanks.

Dan

 

[Jonmo1]

Try

IF(OR(J18<$A$3,J18>=$A$23),"--",LOOKUP(J18,$A$3:$A$21,$D$4:$D$22))

 

[ddub25]

I couldn't get that to work. It would just leave the formula in the cell as text. It didn't reject the formula though.

Dan

 

[Jonmo1]

Well, I did forget to put in the initial = sign...

=IF(OR(J18<$A$3,J18>=$A$23),"--",LOOKUP(J18,$A$3:$A$21,$D$4:$D$22))


Also, make sure the cell you put the formula in is formatted as General, not text.
Then re enter the formula

 

[ddub25]

Thanks Jonmo, thats very useful - much cleaner.

Dan