Excel Formula HELP !!

Russ 0 Reputation points
2026-09-09T22:10:52.4833333+00:00

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

Microsoft 365 and Office | Excel | Other | Windows
0 comments
Answer accepted by question author

AI answer

2026-09-09T22:11:07.0633333+00:00

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.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rory Archibald 19,055 Reputation points Volunteer Moderator
    2026-09-10T07:03:23.6866667+00:00

    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.

    Was this answer helpful?

    0 comments
  2. Russ 0 Reputation points
    2026-09-09T22:17:23.37+00:00

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

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.