# Formula to meet if then condition

#### Brew

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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi, Brew,

try this arrayformula
Code:
``{=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

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

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

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

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

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

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

Hi Brew,

Here is another way.

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

Hope it works.

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
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

[Table-It] version 06 by Erik Van Geit
Code:
``````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

Replies
3
Views
313
Replies
1
Views
128
Replies
25
Views
717
Replies
1
Views
151
Replies
18
Views
585

1,207,094
Messages
6,076,550
Members
446,212
Latest member
KJAYPAL200

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back