formula If, And, Or

[Lady Zero]

Hello,
my formula needs to check if (B2=1 and A2 starts with '280') if this is so, write ok or else check if (B2=2 and A2 starts with '474') if this is so, write OK, or else check if (B2=3 and A2 starts with '860') if this is so, write ok; or else check if (B2=4 and A5 starts with '358') if this is so, write ok, or else check if (B2=0 and A2 starts with '358') if this is so, write ok; if none of this is so, write NOK.
So there are 5 'if and' formules and 'or else' parts added.

thanks for the help.
I cannot add my excel because it contains confidential information.

KR
LZ

 

[Joe4]

Welcome to the Board!

You can just use OR and ANDs, i.e.

=IF(OR(AND(A2=1,LEFT(B2,3)="280"),AND(A2=2,LEFT(B2,3)="474"),AND(A2=3,LEFT(B2,3)="860"),AND(A2=4,LEFT(B2,3)="358"),AND(A2=0,LEFT(B2,3)="358")),"OK","")

or more simply:

=IF(OR(AND(A2=1,LEFT(B2,3)="280"),AND(A2=2,LEFT(B2,3)="474"),AND(A2=3,LEFT(B2,3)="860"),AND(OR(A2=4,A2=0),LEFT(B2,3)="358")),"OK","")

Note that if the value in A2 are text entries and not mumeric, you will need to wrap the number in quotes, i.e. change those formulas from looking like this:

A2=1

to looking like this:

A2="1"

 

[ranman256]

Public Function ValidateB2()
Select Case True
    Case Range("b2").Value = 1 And Left(Range("a2").Value, 3) = "280"
       MsgBox "OK"
    Case Range("b2").Value = 2 And Left(Range("a2").Value, 3) = "474"
       MsgBox "OK"
    Case Range("b2").Value = 3 And Left(Range("a2").Value, 3) = "860"
       MsgBox "OK"
    Case Range("b2").Value = 4 And Left(Range("a5").Value, 3) = "358"
       MsgBox "OK"
    Case Range("b2").Value = 0 And Left(Range("a2").Value, 3) = "358"
       MsgBox "OK"
    Case Else
       MsgBox "NOK"
End Select
End Function
[code]/

 

[Joe4]

Public Function ValidateB2()
Select Case True
    Case Range("b2").Value = 1 And Left(Range("a2").Value, 3) = "280"
       MsgBox "OK"
    Case Range("b2").Value = 2 And Left(Range("a2").Value, 3) = "474"
       MsgBox "OK"
    Case Range("b2").Value = 3 And Left(Range("a2").Value, 3) = "860"
       MsgBox "OK"
    Case Range("b2").Value = 4 And Left(Range("a5").Value, 3) = "358"
       MsgBox "OK"
    Case Range("b2").Value = 0 And Left(Range("a2").Value, 3) = "358"
       MsgBox "OK"
    Case Else
       MsgBox "NOK"
End Select
End Function
[code]/

No VBA is necessary. Very easy to do with Excel formulas.

 

[hagia_sofia]

A slightly shorter option:

=IF(OR((B2&"-"&LEFT(A2,3))={"1-280","2-474","3-860","4-358","0-358"}),"OK","NOK")

 

[Lady Zero]

Welcome to the Board!

You can just use OR and ANDs, i.e.

=IF(OR(AND(A2=1,LEFT(B2,3)="280"),AND(A2=2,LEFT(B2,3)="474"),AND(A2=3,LEFT(B2,3)="860"),AND(A2=4,LEFT(B2,3)="358"),AND(A2=0,LEFT(B2,3)="358")),"OK","")

or more simply:

=IF(OR(AND(A2=1,LEFT(B2,3)="280"),AND(A2=2,LEFT(B2,3)="474"),AND(A2=3,LEFT(B2,3)="860"),AND(OR(A2=4,A2=0),LEFT(B2,3)="358")),"OK","")

Note that if the value in A2 are text entries and not mumeric, you will need to wrap the number in quotes, i.e. change those formulas from looking like this:

A2=1

to looking like this:

A2="1"

I had to change the A's to B's and all the , to ; -> I works perfect now. Thank you very much!

 

[Joe4]

I had to change the A's to B's and all the , to ; -> I works perfect now. Thank you very much!

Ah, sorry! I seem to have a little letter dyslexia this morning!
Anyway, glad you got it worked out!