Formula to meet if then condition

[Brew]

How do I create a formula that if du8 or dv8 or dw8 is equal to 1 or 2 or 3, then Yes

 

[erik.van.geit]

Hi, Brew,

try this arrayformula

{=IF(SUM(--ISNA(MATCH(DU8:DW8,{1,2,3},0)))<>3,"YES","NO")}

some examples
   DU DV DW DX  
 8 a  2  c  YES 
 9 1        YES 
10 a  b  c  NO  
11 3  b  c  YES 
12 a  b  2  YES 

Blad1
[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[pgc01]

Hi Brew

I'm not sure if I understood well.

If you mean that it is enough that one of the three cels has 1 or 2 or 3 then:

=IF(SUMPRODUCT(--(DU8:DW8={1;2;3})),"Yes")

If all 3 cells must have 1 or 2 or 3

=IF(SUMPRODUCT(--(DU8:DW8={1;2;3}))=3,"Yes")

Hope this helps
PGC

 

[erik.van.geit]

Hi, PGC,

I would have expected that your formula checks
1st cell = 1
or
2nd cell = 2
or
3rd cell = 3

but any value (out of 1,2,3) in any cell was found as true

so that's a nice formula, much simpler than mine !
Erik

 

[pgc01]

Hi Erik

I would have expected that your formula checks
1st cell = 1
or
2nd cell = 2
or
3rd cell = 3

That would be the case if I had used the array {1,2,3}. However since DU8:DW8 is a row vector I used the column vector {1;2;3} forcing excel to generate a 3x3 matrix where each cell value is compared with each array value. If you select DU8:DW8={1;2;3} in the formula bar and use F9 you'll see the 9 values

Cheers
PGC

 

[erik.van.geit]

cheers, PGC!

I was confused by the syntax
{1;2;3} the semicolon is my regional separator substituting your comma

your formula is translated as ... {1\2\3}

the F9-trick was new to me, thanks
Erik

 

[Brew]

Thanks gentlemen, I agree with Erik, pgc01 your first formula is a great solution

 

[vane0326]

Hi Brew,



Here is another way.


=IF(OR(INDEX(DU8:DW8={1,2,3},0)),"Yes")


Hope it works.

 

[erik.van.geit]

Hi, vane0326,

your formula is useful when you need 1 in 1st cell, 2 in 2nd or 3 in 3rd
Brew wanted 1, 2, 3 in ANY cell
I added NO for clarity
   DU DV DW DX  DY  
 8 a  b  c  No  No  
 9 1        Yes Yes 
10    2     Yes Yes 
11       3  Yes Yes 
12 3        No  Yes 
13    1     No  Yes 
14       2  No  Yes 

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE     FORMULA (1st cell)
DX8:DX14  =IF(OR(INDEX(DU8:DW8={1,2,3},0)),"Yes","No")
DY8:DY14  =IF(SUMPRODUCT(--(DU8:DW8={1;2;3})),"Yes","No")

[Table-It] version 06 by Erik Van Geit

best regards,
Erik