49

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.

54

Please try:

=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.

Ref: Documentation for countif

23

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

3

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 at 16:11
-3

Use GoogleSpreadsheet and there

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

and then connect document via API.

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

protected by Community Nov 18 '17 at 21:58

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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