multiple IF statements in formula - exceeds max allowed

Hello,
I am trying to make the following formula to work but I get: "This formula uses more levels of nesting than you can use in the current file format":

=IF(BB23<=$BB$8,$AZ$8,IF(BB23<=$BB$9,$AZ$9,IF(BB23<=$BB$10,$AZ$10,IF(BB23<=$BB$11,$AZ$11,IF(BB23<=$BB$12,$AZ$12,IF(BB23<=$BB$13,$AZ$13,IF(BB23<=$BB$14,$AZ$14,IF(BB23<=$BB$15,$AZ$15,IF(BB23<=$BB$16,$AZ$16,IF(BB23<=$BB$17,$AZ$17,IF(BB23<=$BB$18,$AZ$18,IF(BB23<=$BB$19,$AZ$19,IF(BB23<=$BB$20,$AZ$20,IF(BB23<=$BB$21,$AZ$21,$AZ$22))))))))))))))

Any suggestions on how I could simplify this multiple IF statement formula or break it down into two separate cells?

Thanks in advance for any help provided.

You could try this approach instead:

=IFERROR(INDEX($AZ$8:$AZ$21,MATCH(BB23,$BB$8:$BB$21)),$AZ$22)

EXCEL 2007 and later supports more levels of nesting than you have, so perhaps you are using an .xls file rather than an .xlsx.

Hope this helps.

Pete

Thank you very much for your replies. I am not familiar with the functions that you are suggesting but unfortunately none are giving me the proper results.
This is the data on the cell ranges:

28moayu.jpg

If BB23 = 9.10, I would expect the formula to return: B4 (condition being met: IF(BB23<=$BB$11,$AZ$11... )

However,

=IFERROR(INDEX($AZ$8:$AZ$21,MATCH(BB23,$BB$8:$BB$21)),$AZ$22) is returning B3

=INDEX($AZ$8:$AZ$22,MIN(IF($BB$8:$BB$22>=BB29,ROW($BB$8:$BB$22),""))) is returning #VALUE!

=INDEX($AZ$8:$AZ$22,AGGREGATE(15,6,ROW($8:$22)/($BB$8:$BB$22>=BB23),1)) is returning B11

Maybe there is something else I would need to tweak in these formulas?
By the way, the file was originally created in Excel 2013 and I am now editing it in Excel 2016, in .xlsx format, but I still get the "levels of nesting" error.

Thank you,

@ animateme,
look at the attachment. It seems to me, that both formulas meet your expectations.
Regards.

@ AliGW, here's my correction.

Array formula (Ctrl + Shift + Enter)
=IF(INDEX(AZ1:AZ22,MIN(IF(BB8:BB22>=BB23,ROW(BB8:BB22),"")))=0,AZ22,INDEX(AZ1:AZ22,MIN(IF(BB8:BB22>=BB23,ROW(BB8:BB22),""))))

Normal formula
=IFERROR(INDEX(AZ1:AZ22,AGGREGATE(15,6,ROW(8:22)/(BB8:BB22>=BB23),1)),AZ22)

Regards.

Originally Posted by animateme

... Maybe there is something else I would need to tweak in these formulas? ...

Try this variation of my formula:

=IF(BB3<=$BB$8,$AZ$8,IFERROR(INDEX($AZ$8:$AZ$21,MATCH(BB23,$BB$8:$BB$21,IF(ISNUMBER(MATCH(BB23,$BB$8:$BB$21,0)),0,1))),$AZ$22))

Hope this helps.

Pete

EDIT: Next time, it would help if you attached a sample Excel workbook, rather than a picture of one.

Thank you again for your responses.

This is the one that worked perfectly:

Normal formula
=IFERROR(INDEX(AZ1:AZ22,AGGREGATE(15,6,ROW(8:22)/(BB8:BB22>=BB23),1)),AZ22)

Thank you!!!