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.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityYou 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.
I want to check if **the value** of a cell exists in a range of cells
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.
0
is super helpful, much easier than having to worry about how the second sheet is sorted.
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")
Use GoogleSpreadsheet and there
=vlookup(C2;A2:B4;2;false)
and then connect document via API.
MATCH
answer from below can be:=NOT(ISNA(MATCH(A1; B1:B10; 0)))
.