Is there a Google Sheets formula that indicates whether a value (e.g., the contents of A1) is in a specified set (e.g., 1, 5, 9)?

Yes, I could do a SWITCH(A1, 1, 1, 5, 1, 9, 1, 0), but that is overly verbose.

I want something terse like ISINSET(A1, 1, 5, 9).

  • Is your data type in A1 numeric or string? And if numeric, is there a definite range of possible values? If so, what is that range? If not, what else can you tell us about the A1 value?
    – Erik Tyler
    Nov 15, 2021 at 20:50
  • Each value will be a positive integer from 1 to around 67 or so, or the cell will be empty
    – XDR
    Nov 15, 2021 at 20:56
  • And is the number set to try to match with listed somewhere in your sheet? Or will it only be listed within the formula? (It would be the professional standard in most cases to list the possible match values somewhere, typically in a separate sheet that may be hidden.) But what is your own situation currently regarding this? In addition, will you be trying to process only one cell — A1 — or a range of cells? (All of this would be easier if we could simply view a sample spreadsheet that represents the goal in a realistic manner.)
    – Erik Tyler
    Nov 15, 2021 at 20:58
  • I could put the numbers in a separate work sheet, but it will probably be easier to just embed them in the formula. There are only around six numbers in the set.
    – XDR
    Nov 15, 2021 at 21:00
  • See edits to my previous comment, particularly: Are you trying to process only A1 for a match, or a number of cells in a range? If the latter, what is the actual range (e.g., B2:B, A2:A100, H3:H)?
    – Erik Tyler
    Nov 15, 2021 at 21:17

2 Answers 2

=arrayformula(or(A1={1, 5, 9}))

enter image description here

  • 1
    That works. Thanks. I'll leave the question open for now, but I'll accept your answer sometime soon assuming no one supplies a more concise answer.
    – XDR
    Nov 15, 2021 at 12:14

There are many ways to do this. Here's one:

=not( isna( match( A1, { 1, 5, 9 }, 0 ) ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.