IF(AND Question

[wizardmagu]

I am trying to build an IF(AND formula that will evaluate the cell and if it falls in between a given range then assign a value. For some reason I have it working on the first 6 but the last 5 are not working. Any ideas?

=IF(AND(B6>=$S$3,B6<=$R$3),$R$2,IF(AND(B6>=$Q$3,B6<=$P$3),$P$2,IF(AND(B6>=$O$3,B6<=$N$3,$N$2),IF(AND(B6>=$M$3,B6<=$L$3,$L$2),$L$2,""))))

 

[Fluff]

You have a closing bracket in the wrong place, try

=IF(AND(B6>=$S$3,B6<=$R$3),$R$2,IF(AND(B6>=$Q$3,B6<=$P$3),$P$2,IF(AND(B6>=$O$3,B6<=$N$3),$N$2,IF(AND(B6>=$M$3,B6<=$L$3,$L$2),$L$2,""))))

 

[wizardmagu]

Update... I used an XLOOKUP to get it to work, but not sure if that is the "right" way to do it.

=XLOOKUP(B6,$L$3:$S$3,$L$2:$S$2,,1)

I would really like to use the IF(AND because it can be a powerful function but wanted to see everyones thoughts?

 

[wizardmagu]

You have a closing bracket in the wrong place, try

=IF(AND(B6>=$S$3,B6<=$R$3),$R$2,IF(AND(B6>=$Q$3,B6<=$P$3),$P$2,IF(AND(B6>=$O$3,B6<=$N$3),$N$2,IF(AND(B6>=$M$3,B6<=$L$3,$L$2),$L$2,""))))

oh my... Thanks for the catch. Also, The last portion of my formula that shows the final $L$2. I am not really sure how it works and wondering if you can explain?

 

[Fluff]

Missed that & it's also wrong, it should be

=IF(AND(B6>=$S$3,B6<=$R$3),$R$2,IF(AND(B6>=$Q$3,B6<=$P$3),$P$2,IF(AND(B6>=$O$3,B6<=$N$3),$N$2,IF(AND(B6>=$M$3,B6<=$L$3),$L$2,""))))