Excel Formula HELP !!

Question

Excel Formula HELP !!

Russ 20

I would like a formula that displays back "Yes" or "No" if the first cell to the left contains a 1, 2 and 3. The first cell could contain multiple 2's for instance ie 2,2,3, but in this case it is missing the number 1 and would like it to display a "No" in this case. As long as the cell to the left has at least one of the following numbers (1,2 or 3) I would like it to display "Yes".

Thank you !!

User's image

0 comments

Answer accepted by question author

AI answer

Use an IF formula that checks whether the cell to the left contains 1, 2, and 3 at least once each.

If the list is in A2, enter this in B2:

=IF(AND(A2<>"",ISNUMBER(SEARCH("1",A2)),ISNUMBER(SEARCH("2",A2)),ISNUMBER(SEARCH("3",A2))),"Yes","No")

How it works:

SEARCH("1",A2) checks whether 1 exists in the cell
SEARCH("2",A2) checks whether 2 exists
SEARCH("3",A2) checks whether 3 exists
AND(...) returns TRUE only if all three are found
IF(...,"Yes","No") converts that result to Yes or No

Based on the examples shown:

2, 2, 3 → No
1, 2, 2, 3 → Yes
1, 2, 2 → No
1, 2 → No
1, 2, 3 → Yes

If the requirement is instead to return Yes when the cell contains any one of 1, 2, or 3, use:

=IF(OR(ISNUMBER(SEARCH("1",A2)),ISNUMBER(SEARCH("2",A2)),ISNUMBER(SEARCH("3",A2))),"Yes","No")

AI-generated content may be incorrect. Read our transparency notes for more information.

2 additional answers

Your answer

Answer 1

Russ 20

=IF(AND(A2<>"",ISNUMBER(SEARCH("1",A2)),ISNUMBER(SEARCH("2",A2)),ISNUMBER(SEARCH("3",A2))),"Yes","No")

0 comments

Answer 2

Rory Archibald 19,055 Volunteer Moderator

You can also take advantage of the fact that COUNT ignores errors (which SEARCH will return if the value is not present) like this:

=IF(COUNT(SEARCH({1,2,3},A2))=3,"Yes","No")

The count will only be 3 if all values are present in the cell.

0 comments