Hi Excel Wizards! I understand excel 2003 has a limit of 8 nested if's, so would be grateful for a solution on how to get round a specific problem. The table of data is approx 5000 lines with Columns A to W. Formula below which works fine, sits in Column X and will be used on all rows. This is only half the formula with a further 8 IF statements required which will be very similar to the ones I've already included. Where it says Next IF Statement 1, I was trying to insert IF(LEFT(F6,2)="RA","Disposals Germany","Support Costs & Income Germany" but it would'nt work.. I could insert the IF statement without the (LEFT(F6,2)="RA" so I'm guessing even the number of nested LEFT functions are limited.Formula =IF(OR(LEFT(F6,1)="E",LEFT(F6,1)="F",LEFT(F6,1)="G",LEFT(F6,3)="NWA")=TRUE,"Double Entry",IF(LEFT(F6,2)="TX","Forex",IF(LEFT(F6,1)="L","Pay",IF(W6="ESG",IF(OR(LEFT(F6,2)="ND",LEFT(F6,2)="NC",LEFT(F6,2)="B"),"Hard FM ESG",IF(LEFT(F6,3)="NBA","Leases ESG",IF(LEFT(F6,2)="RA","Disposals ESG","Support Costs & Income ESG"))),IF(W6="HQ DIO Europe",IF(OR(LEFT(F6,2)="ND",LEFT(F6,2)="NC",LEFT(F6,2)="B"),"Hard FM ESG",IF(LEFT(F6,3)="NBA","Leases Misbooking","Next IF Statement 1")),"Next IF Statement 2")))))
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Nested IF's Excel 2003
- Thread starter MelG
- Start date
-
- Tags
- multiple if nested if
JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
You're be much better off creating a VLOOKUP table elsewhere in your workbook and then referncing that. Your current formula is far too long and excessive to be useful to anyone else having to work through it..
Upvote
0
