Help with MID function, multiple if's

[andy.k]

Please help.

I got following formula:

IF(MID(A238,5,4)="TEST", IF(A237="CTAG",A236,J237)," ")

This works for me, but in first call, i would like to next more ifs, for example:

IF(MID(A238,5,4)="TEST",
IF(MID(A238,5,5)="TEST1",
IF(MID(A238,5,3)="TES,
IF(MID(A238,5,7)="TEST123",

and end with, IF(A237="CTAG",A236,J237)," ")

[etaf]

IF(MID(A238,5,4)="TEST",
IF(MID(A238,5,5)="TEST1",
IF(MID(A238,5,3)="TES,
IF(MID(A238,5,7)="TEST123",

so all those above if true

then use an OR

IF(OR(MID(A238,5,4)="TEST", MID(A238,5,5)="TEST1", MID(A238,5,3)="TES", MID(A238,5,7)="TEST123") , IF(A237="CTAG",A236,J237)," ")

Or do the first 3 characters always = TES ?

[Harribone]

In your original formula where you have " " replace with a new if:

=IF(MID(A238,5,4)="TEST", IF(A237="CTAG",A236,J237),IF(MID(A238,5,5)="TEST1",A,B))

Where A is what you want to do if that is true and B is when false. Again replace B with another IF statement.

Or if you want the same output for each test you can try:

=IF(OR(MID(A238,5,4)="TEST",IF(MID(A238,5,5)="TEST1",IF(MID(A238,5,3)="TES,IF(MID(A238,5,7)="TEST123"),IF(A237="CTAG",A236,J237)," ")

As long as one of your checks is true then it will move onto the A237 bit.

[Harribone]

As etaf has pointed out if you go down the OR route because you want the same output from your formula for each check you may aswell just check for TES which wil cover all your given situations

[andy.k]

Thanks, but, my checks do not have same names. Will try OR function. Thanks!

[andy.k]

I cannot get that OR formula to work, error states that formula has error... any ideas?

[FDibbins]

Try this. You can skip the test for "Tes", they all begin with that. Then, start with the shortest, and work towards the longest - that way it will work through the word progressively...

IF(OR(MID(A238,5,4)="TEST",MID(A238,5,5)="TEST1",MID(A238,5,7)="TEST123"),IF(A237="CTAG",A236,J237)," ")

[etaf]

I cannot get that OR formula to work, error states that formula has error... any ideas?

works ok for me