Function to test whether any individual cell in a range of cells contains a particular value

[peskatoon]

My spreadsheet has a column containing the following values, one value per cell:

1.1
2.5
3.2
4.7
5.3

I need a function to test whether any individual cell in that range equals a particular value, e.g.

if any cell in the range equals 4.7, return true
if any cell in the range equals 0.0, return false

I initially turned to find and search, but these do not appear to do what I want them to do. Is there a function that does?

[anon73440385]

Hello

assuming

• data array is in range A1:A5
• search criterion is in Cell C1

use formula: =IFNA(MATCH(C1;A1:A5;0);0) and format the cell containing this formula using Format Code BOOLEAN

See also the following sample file: MatchInArray.ods

Note

Solution is based on the fact that all values >=1 (result of MATCH()) are boolean TRUE in Calc

Hope that helps

[peskatoon]

Match! That is what I was looking for. Thank you!