Returning Specific Data on Examining a Range

March 02, 2002 - by Juan Pablo Gonzalez

Chris asks:

What I actually need is a function that will examine a range of cells and returns a certain value if any one of the cells in the range contains a certain word. Is there such a function?

Assuming this data is in A1:A4

And you want to test for the existance of "Juan", (Located in C1), you could use, for example:

{=IF(LEN(C1)*OR(ISNUMBER(SEARCH("*"&C1&"*",A1:A4))),"Exists","Doesn't Exist")}

=IF(LEN(C1)*ISNUMBER(MATCH("*"&C1&"*",A1:A4,0)),"Exists","Doesn't Exist")

=IF(LEN(C1)*COUNTIF(A1:A4,"*"&C1&"*"),"Exists","Doesn't Exist")

The first one is a CSE formula (Must be entered pressing Control Shift Enter), the second and third one are regular formulas.

First one is also case sensitive.