How to check if value is in range of cells?

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.

``````=countif(G:G,6)
``````

if you want to find the number `6` in ColumnG:G and/or

``````=countif(A1:B7,"d")
``````

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

By find I really mean count the number of instances in the chosen range.

• The answer from @Peter Smulders should be marked as the accepted answer because You should never use countif function – transang Oct 6 '19 at 12:53
• @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 at 21:27
• @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` – transang Jan 21 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 at 10:04
• =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 at 17:26

You could try `MATCH`:

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

``````

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.

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")`

• 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 '19 at 16:11

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