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.

• Were you able to apply my solutions? Jun 6 '13 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 '21 at 17:17

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.

• The answer from @Peter Smulders should be marked as the accepted answer because You should never use countif function 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. Jan 20 '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 Jan 21 '20 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. Jan 21 '20 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 Mar 22 '20 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.

• The 0 is super helpful, much easier than having to worry about how the second sheet is sorted. Nov 25 '20 at 1:54

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