53

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.

59

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

25

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 '19 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

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