If cell = this, show this.

[Asw091]

Hi guys, from looking up formulas online I made this, where am I going wrong? it just returns an error.

=IF((d5)={<90%,<95%,<100%,>105%,<110%,<115%,<120%,<125%,<130%,<135%,>135%)},{C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33}))

It may be something so simple but I can't for the life of me figure it out.

thanks.

 

[AlanY]

the formula should looks like

=if(D5<90%,C23,if(D5<95%,C24,if(D5<100%, etc etc ))))

or

=LOOKUP(D5-0.0001,{0,0.9,0.95,1,1.05,1.1,1.15,1.2,1.25,1.3,1.35},C23:C33)

 

[Joe4]

I am not sure if it changed with recent versions of Excel, but you could only nest IF statements up to 7 levels of Excel in the past.

Take a look at some options:
http://ccm.net/faq/26886-nesting-more-than-7-if-statements-in-an-excel-function
https://exceljet.net/nested-ifs

 

[Asw091]

=LOOKUP(D5-0.0001,{0,0.9,0.95,1,1.05,1.1,1.15,1.2,1.25,1.3,1.35},C23:C33)

Awesome, that's what I was after, thanks.

 

[thisoldman]

I am not sure if it changed with recent versions of Excel, but you could only nest IF statements up to 7 levels of Excel in the past.

From Excel 2007 onward, you can nest up to 64 IF statements.

https://support.office.com/en-us/ar...4ba-aeb5-4ef99da241c8?ui=en-US&rs=en-US&ad=US

 

[Joe4]

From Excel 2007 onward, you can nest up to 64 IF statements.

Good to know. I thought that it had changed, but wasn't sure with which version. I am surprised it is that far back. I thought it would be something like 2013 or 2016.

Thanks