Excel Formula Error

Hi all,

I have a non-functioning formula in Excel again - it's a big one!

=IF(ISERROR(-IF(COUNTIF(C4,"W???U"),MID(C4,2,3),IF((C520="WU100"),VLOOKUP(C520,Redemptions!A:D,4,0),IF((RIGHT(C520,3)="DMU"),VLOOKUP(C520,Redemptions!A:D,4,0),-IF(COUNTIF(C4,"O??A"),VLOOKUP(C4,Redemptions!A:D,4,0),-IF(COUNTIF(C4,"O???A"),VLOOKUP(C4,Redemptions!A:D,4,0),IF(OR(LEFT(C4,2)={"DN","WA","MC","LW","LB","DU","LC","IL"}),MID(C4,3,3),0))))))),0,-IF(COUNTIF(C4,"W???U"),MID(C4,2,3),IF(C520="WU100"),VLOOKUP(C520,Redemptions!A:D,4,0),IF(RIGHT(C520,3)="DMU"),VLOOKUP(C520,Redemptions!A:D,4,0),-IF(COUNTIF(C4,"O??A"),VLOOKUP(C4,Redemptions!A:D,4,0),-IF(COUNTIF(C4,"O???A"),VLOOKUP(C4,Redemptions!A:D,4,0),IF(OR(LEFT(C4,2)={"DN","WA","MC","LW","LB","DU","LC","IL"}),MID(C4,3,3),0)))))

All I have done is added a couple of new constraints (highlighted in bold) to a previous formula I had that DID work:

=IF(ISERROR(-IF(COUNTIF(C4,"W???U"),MID(C4,2,3),-IF(COUNTIF(C4,"O??A"),VLOOKUP(C4,Redemptions!A:D,4,0),-IF(COUNTIF(C4,"O???A"),VLOOKUP(C4,Redemptions!A:D,4,0),IF(OR(LEFT(C4,2)={"DN","WA","MC","LW","LB","DU","LC","IL"}),MID(C4,3,3),0))))),0,-IF(COUNTIF(C4,"W???U"),MID(C4,2,3),-IF(COUNTIF(C4,"O??A"),VLOOKUP(C4,Redemptions!A:D,4,0),-IF(COUNTIF(C4,"O???A"),VLOOKUP(C4,Redemptions!A:D,4,0),IF(OR(LEFT(C4,2)={"DN","WA","MC","LW","LB","DU","LC","IL"}),MID(C4,3,3),0)))))

Could somebody please tell me a correct formula and also, to help for the future, why the added new constraints bring back an error. I think it has something to do with parentheses but am not sure which ones and where or why.

Thanks in advance!

Can you upload example workbook.. There must be simplier solution

It's ok thanks.

I just decided to not try and be too clever for myself and do a set of simple manual vlookups.

Thanks for the advice though

Unfortunately not quite in the way I had wanted it to.

Thanks all the same