115

In a Google Sheets spreadsheet, I want to check if the value of a cell exists in a range of cells. How can I do this?

Just like you would be able to do "if value in list_of_values" in Python for example.

Improve this question
2
  • 1
    Were you able to apply my solutions?
    – Jacob Jan Tuinstra
    Jun 6, 2013 at 11:55
  • If booleans are the desired output, a shorter variation of the MATCH answer from below can be: =NOT(ISNA(MATCH(A1; B1:B10; 0))).
    – Moh
    Jun 20, 2021 at 17:17

4 Answers 4

Reset to default
120

You could use COUNTIF.

For example:

=COUNTIF(G:G,6) > 0

if you want to find the number 6 in the range G:G, and/or

=COUNTIF(A1:B7,"d") > 0

if you want to find d in the first seven rows of the first two columns.

COUNTIF will return the number of instances in its given range that are equal to its given value. It can also do a bit more complex conditioning (e.g all values higher than 6, or containing the letter A); see the docs for more info.

Improve this answer
5
  • @transang as author (I believe you) wrote in the linked article: "never use countif when the criterion parameter (the second parameter) is dynamic". Emphasis on dynamic. The OP asks for a static one.
    – meridius
    Jan 20, 2020 at 21:27
  • 1
    @meridius Yes. I am the author of the linked article. The OP does not mention whether the value is static or dynamic. I want to check if **the value** of a cell exists in a range of cells
    – Sang
    Jan 21, 2020 at 6:02
  • @transang sorry, I got what OP asked and what pnuts suggested mixed up. This answer is for static case, in a dynamic one you're of course right.
    – meridius
    Jan 21, 2020 at 10:04
  • 1
    =COUNTIF(H85:H90; "Pending") Some locales require the use of a semi-colon as an argument separator. support.google.com/docs/thread/18510720?hl=en
    – null1941
    Mar 22, 2020 at 17:26
  • @transang still, your title says you should never use it, which is in direct contradiction with what you wrote in the body. Further, you have made a circular reference and presented it as an existing rule instead of something you came up with in response to this very answer. The mods should remove that link.
    – Corey
    Sep 12, 2022 at 5:24
49

You could try MATCH:

A1     = [Value you are trying to find]
B1:B10 = [Range you are searching in]

=IF(ISERROR(MATCH(A1;B1:B10;0));"Not found";"Found")

You can replace the A1 with formulas that return a value to search for. The 0 ensures actual search, not presupposing an ordered search range.

Ref: Documentation for MATCH

Improve this answer
1
  • 1
    The 0 is super helpful, much easier than having to worry about how the second sheet is sorted.
    – CWSites
    Nov 25, 2020 at 1:54
4

User can do that in different ways (any of the following may apply):

=IFERROR(IF(UNIQUE(FILTER(B2:B,B2:B="text"))="text";"Found";"Not Found");"Not Found")

=IFERROR(IF(COUNTIF(ARRAYFORMULA(range="text"),TRUE)>0;"Found";"Not Found");"Not Found")

=IFERROR(IF(COUNTIF(ARRAYFORMULA(FIND("text", FILTER(range,range<>""))),">"&0)>0;"Found";"Not Found");"Not Found")

Improve this answer
1
  • Thanks. How can I do something similar without exact match but rather by checking if "text" is contained as part of a string in any of the cells of the B2:B range?
    – Giacomo
    Feb 12, 2019 at 16:11
-2

Use GoogleSpreadsheet and there 

=vlookup(C2;A2:B4;2;false)

and then connect document via API.

Improve this answer
2
  • 1
    Can anyone explain why this is "-3" ?
    – meeDamian
    Mar 12, 2014 at 16:34
  • 1
    @meeDamian because if the value is not found it will raise an error
    – Enrico
    Jan 25, 2018 at 15:50

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