Indicating whether a value is in a specified set

Question

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

ZygD · Accepted Answer · 2021-11-15 12:09:25Z

1

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

answered Nov 15, 2021 at 12:09

ZygD

1,5003 gold badges15 silver badges22 bronze badges

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

Add a comment |

doubleunary · Accepted Answer · 2021-11-15 14:02:07Z

0

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

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

answered Nov 15, 2021 at 14:02

doubleunary

13.3k4 gold badges17 silver badges49 bronze badges

Add a comment |

2 Answers 2