Formula Does not Work

[lozg43]

Hi,

Could somebody please tell me why the following Vlookup is not working:

=IF(AND(LEFT(C4,1)="W",RIGHT(C4,1)="U"),-MID(C4,2,3),IF(AND(LEFT(C4,2)="DN",LEN(C4)=4,-RIGHT(C4,2),IF(AND(LEFT(C4,2)="DN",LEN(C4)=5,-RIGHT(C4,3),IF(LEFT(C4,2)="WA",-RIGHT(C4,3),IF(LEFT(C4,2)="MC",-RIGHT(C4,3),0))))

Where the data in column C is a 4 or 5 digit code.

Thanks!

[NBVC]

Missing closing parentheses for the AND() functions..

=IF(AND(LEFT(C4,1)="W",RIGHT(C4,1)="U"),-MID(C4,2,3),IF(AND(LEFT(C4,2)="DN",LEN(C4)=4,-RIGHT(C4,2)),IF(AND(LEFT(C4,2)="DN",LEN(C4)=5,-RIGHT(C4,3)),IF(LEFT(C4,2)="WA",-RIGHT(C4,3),IF(LEFT(C4,2)="MC",-RIGHT(C4,3),0)))))

[ChemistB]

You're missing a few parentheses. I believe it should read

=IF(AND(LEFT(C4,1)="W",RIGHT(C4,1)="U"),-MID(C4,2,3),IF(AND(LEFT(C4,2)="DN",LEN(C4)=4,-RIGHT(C4,2)),IF(AND(LEFT(C4,2)="DN",LEN(C4)=5,-RIGHT(C4,3)),IF(LEFT(C4,2)="WA",-RIGHT(C4,3),IF(LEFT(C4,2)="MC",-RIGHT(C4,3),0)))))

Does that work for you?

[daddylonglegs]

Try this...

=-IF(COUNTIF(C4,"W???U"),MID(C4,2,3),IF(OR(LEFT(C4,2)={"DN","WA","MC"}),MID(C4,3,3),0))

[lozg43]

Brilliant!

Thanks all for the suggestions.

You really know your stuff!!