How to determine if a group of numbers is contained in a cell.

[JayZ]

Can someone help me with the following formula? It does not work and I don't know what I'm doing wrong.

=IF(OR(AND(F10="black",A10={1,2,4,5,7,8,10,11,13,14,16,17,19,20,22,23,25,26,28,29,31,32,34,35}),AND(F10="red",A10={1,3,4,6,7,9,10,12,13,15,16,18,19,21,22,24,25,27,28,30,31,33,34,36})),1,-2)

Am I using the right formula to determine if a group of numbers is contained in a cell?

Thanks so much.

John

 

[mikerickson]

Do you want all those numbers somewhere in the cell?

Something like =AND(ISNUMBER(FIND(","&{1,3,5}&",", ","&D5&","))) will tell if all the numbers 1,3 and 5 are in D5 e.g. "1,34,3,5"

Or do you want to know if any of the number are in the cell
=ISNUMBER(MATCH(D2,{1,3,5},0) will tell if any of those numbers are in D2

 

[JayZ]

Thanks so much for your reply.

Here's an example of what I'm looking for:

I want to know if any given cell contains a set of numbers that I establish.

1. Cell A1 has the number 16.
2. I want to know if cell A1 contains 1,2,3,6,12,15,16,25,etc ......
3. If Cell A1 contains the set of numbers that I have established, then the answer is "YES" or whatever I want it to say.

I hope that helps.

I am grateful for your quick reply and assistance.

John

 

[mikerickson]

Cell A1 has 16
It does not contain 1,2,3,6,12,15,16,25

Therefore, the answer is not "YES"

If that is what you want to happen, use the test =AND(ISNUMBER(FIND(","&{1,2,3,6,12,15,16,25}&",", ","&D5&",")))

Cell A1 has 16
It contains one of 1,2,3,6,12,15,16,25

Therefore, the answer is "YES"
If that is what you want to happen, use the test
=ISNUMBER(MATCH(D2,{1,2,3,6,12,15,16,25},0)

 

[JayZ]

Cell A1 has 16
It contains one of 1,2,3,6,12,15,16,25

Therefore, the answer is "YES"
If that is what you want to happen, use the test
=ISNUMBER(MATCH(D2,{1,2,3,6,12,15,16,25},0)

So, this is what I'm looking for. A couple of questions:

1. How do I add an OR to this?
2. So, in addition to the above formula, I am looking to also say if A1 contains one of 1,2,3, etc. or one of 2,5,6, etc.
3. There are 2 sets of numbers I have (some are duplicates). I want to be able to show a "YES" if A1 is contained in either of the 2 sets of numbers.

Hope I made it clear.

Thanks,

 

[mikerickson]

Try
=OR(ISNUMBER(MATCH(A1, oneArray, 0)),ISNUMBER(MATCH(A1, otherArray, 0)))