Nested IF's Excel 2003

[MelG]

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")))))

 

[JackDanIce]

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..

 

[Richynero]

Maybe try creating a UDF using Select Case in VBA.