PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't Acce
 

Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula.
(Note: Range names are used throughout, and they are all valid.)

=IF(O3<"","",
IF(L3=9,AT3,
IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)),
IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)),
IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)),
IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)),
IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),"")))))))

PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't Acce
 

You can only use seven levels of nested functions in Excel.

--
Regards,
Luc.

"Festina Lente"


"Paige" wrote:

Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula.
(Note: Range names are used throughout, and they are all valid.)

=IF(O3<"","",
IF(L3=9,AT3,
IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)),
IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)),
IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)),
IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)),
IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),"")))))))

PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't Acce
 

Use VLOOKUP function instead


"Paige" wrote:

Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula.
(Note: Range names are used throughout, and they are all valid.)

=IF(O3<"","",
IF(L3=9,AT3,
IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)),
IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)),
IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)),
IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)),
IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),"")))))))

PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't
 

Maybe that's why I'm confused; I only used 7 'if' statements, isn't that what
they are talking about re the 7 limit?

"Teethless mama" wrote:

Use VLOOKUP function instead


"Paige" wrote:

Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula.
(Note: Range names are used throughout, and they are all valid.)

=IF(O3<"","",
IF(L3=9,AT3,
IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)),
IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)),
IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)),
IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)),
IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),"")))))))

PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't
 

Your OFFSET(...MATCH(..)) adds two more levels.



"Paige" wrote:

Maybe that's why I'm confused; I only used 7 'if' statements, isn't that what
they are talking about re the 7 limit?

"Teethless mama" wrote:

Use VLOOKUP function instead


"Paige" wrote:

Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula.
(Note: Range names are used throughout, and they are all valid.)

=IF(O3<"","",
IF(L3=9,AT3,
IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)),
IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)),
IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)),
IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)),
IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),"")))))))

PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't
 

Thanks, did not realize that. Thank you all for your help; have a great day!

"JMB" wrote:

Your OFFSET(...MATCH(..)) adds two more levels.



"Paige" wrote:

Maybe that's why I'm confused; I only used 7 'if' statements, isn't that what
they are talking about re the 7 limit?

"Teethless mama" wrote:

Use VLOOKUP function instead


"Paige" wrote:

Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula.
(Note: Range names are used throughout, and they are all valid.)

=IF(O3<"","",
IF(L3=9,AT3,
IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)),
IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)),
IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)),
IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)),
IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),"")))))))