Help with complex formula

G

Guest

Well it's complex for me :)

I have created the following formula which in the main works well, but I am
wanting to enhance it further with other criteria below and struggling.

=IF(M22="","",(IF(LEFT(M22,2)="XD",VLOOKUP(M22,'\\WYP_GEN_1\EA_DIV$\Duties\[Callsigns.xls]CollarNo'!$A$1:$D$100,4,FALSE),(IF(LEFT(M22,3)="TW1",CONCATENATE("TW1",RIGHT(M22,2)),(IF(LEFT(M22,3)="STW",CONCATENATE("STW",RIGHT(M22,3)),(IF(LEFT(M22,3)="CS1",CONCATENATE("CS01",RIGHT(M22,2)),(IF(LEN(M22)=2,CONCATENATE("00",M22),IF(LEN(M22)=3,CONCATENATE("0",M22),(TEXT(M22,"0"))))))))))))))

I want to be able to include the following as well:-

IF(LEFT(M22,3)="CS2",CONCATENATE("CS02",RIGHT(M22,2))

&

IF(LEFT(M22,3)="CS3",CONCATENATE("CS03",RIGHT(M22,2))

&

IF(LEFT(M22,1)="S",RIGHT(M22,4)

Any assistance would be greatly appreciated, thanks
 
M

Max

Here's one try ..

Set-up this lookup table in say, X20:Y24

Put in X20:X24

CS3
CS2
TW1
STW
CS1

Put in:

Y20: =CONCATENATE("CS03",RIGHT(M22,2))
Y21: =CONCATENATE("CS02",RIGHT(M22,2))
Y22: =CONCATENATE("TW1",RIGHT(M22,2))
Y23: =CONCATENATE("STW",RIGHT(M22,3))
Y24: =CONCATENATE("CS01",RIGHT(M22,2))

The above concept attempts to group all the
"LEFT(M22,3)" checks into a single lookup table
which we could then evaluate via say
an INDEX(MATCH(..)) formula with error trap like:

[ :
=IF(ISNA(MATCH(LEFT(M22,3),$X$20:$X$24,0)),"",INDEX($Y$20:$Y$24,MATCH(LEFT(M
22,3),$X$20:$X$24,0))) ]

Now try in say, N23:

=IF(M22="","",(IF(LEFT(M22,2)="XD",VLOOKUP(M22,'\\WYP_GEN_1\EA_DIV$\Duties\[
Callsigns.xls]CollarNo'!$A$1:$D$100,4,FALSE),IF(ISNA(MATCH(LEFT(M22,3),$X$20
:$X$24,0)),IF(LEN(M22)=2,CONCATENATE("00",M22),IF(LEN(M22)<>3,INDEX($Y$20:$Y
$24,MATCH(LEFT(M22,3),$X$20:$X$24,0)),IF(LEN(M22)=3,CONCATENATE("0",M22),(TE
XT(M22,"0")))))))))

Above formula lightly tested only for acceptance in-cell
(i.e. the # of left/right parens are ok)

Test it out on your sheet with some inputs / expected results ..
(.. hope it works <g>)
 
M

Max

Sorry, believe I inadvertently left out one of your specs earlier:
"=IF(LEFT(M22,1)="S",RIGHT(M22,4)"

and think there was also a logic error or two somewhere in the suggested
formula

Try instead in N22, the revised formula:

=IF(M22="","",IF(LEFT(M22,1)="S",RIGHT(M22,4),IF(LEFT(M22,2)="XD",VLOOKUP(M2
2,'\\WYP_GEN_1\EA_DIV$\Duties\[Callsigns.xls]CollarNo'!$A$1:$D$100,4,FALSE),
IF(NOT(ISNA(MATCH(LEFT(M22,3),$X$20:$X$24,0))),INDEX($Y$20:$Y$24,MATCH(LEFT(
M22,3),$X$20:$X$24,0)),IF(LEN(M22)=2,CONCATENATE("00",M22),IF(LEN(M22)=3,CON
CATENATE("0",M22),(TEXT(M22,"0"))))))))
 
Top