# Search cell value in a range

1. ## Search cell value in a range

Hi all,

I need a formula to return a True / False value from searching a cell value in a range of data. As an example:

Cell Ref: 12345

Data Range: A1:Z500

Can a formula return a 'True' value if that number - 12345 - is located somewhere in the data range?

Thank you,

Lee.  Register To Reply

2. ## Re: Search cell value in a range

Lee

Try this.

=COUNTIF(A1:Z500, 12345)>0  Register To Reply

3. ## Re: Search cell value in a range

=COUNTIF(A1:Z500,12345)>0

This will look for numbers
If TEXT then "ABC"  Register To Reply

4. ## Re: Search cell value in a range

Try:

=COUNTIF(A1:Z500,12345)>0  Register To Reply

5. ## Re: Search cell value in a range

Hi Norie,

Thanks. I think I need to clarify further.

The cell values are unique; they could be 86867, or 12345 or something else. Is it possible to search this number in a string text in the data range? Is this possible?

Thanks again,

Lee.  Register To Reply

6. ## Re: Search cell value in a range

Hi LMills77,

You can try sumproduct:
HTML Code:
``=SUMPRODUCT(--(A1:Z500 = 86867))>0``
Hope this helps  Register To Reply

7. ## Re: Search cell value in a range

If you want to do a partial searching:

for value
=OR(ISNUMBER(SEARCH(12345,A1:Z500)))

or for text

=OR(ISNUMBER(SEARCH("12345",A1:Z500)))  Register To Reply

8. ## Re: Search cell value in a range

Hi DL,

Yeah, it's kind of worked but it's only picked up certain results. I have other cell values it hasn't picked up. Trying to figure out why it hasn't?

I've put the formula in as:

=SUMPRODUCT(--EXACT(A1:Z500, cell reference))>0

Thanks again,

Lee.  Register To Reply

9. ## Re: Search cell value in a range

Hi Lmills77,

if you're dragging this formula, you need to lock the range.

PHP Code:
``` =SUMPRODUCT(--EXACT(\$A\$1:\$Z\$500, cell reference))>0  ```
If you're not dragging it down please try:

PHP Code:
``` =SUMPRODUCT(--(\$A\$1:\$Z\$500 = cell reference))>0  ```

Lastly if your search is a partial text 1234 and between text EX: AB1234CD
Then try

=MATCH("*" & 1234 & "*",\$A\$1:\$Z\$500,) > 0

Hope any of these helps  Register To Reply

10. ## Re: Search cell value in a range Originally Posted by bebo021999 If you want to do a partial searching:

for value
=OR(ISNUMBER(SEARCH(12345,A1:Z500)))

or for text

=OR(ISNUMBER(SEARCH("12345",A1:Z500)))
In attachment you find the combination for text and value  Register To Reply

11. ## Re: Search cell value in a range

Thanks very much DL. This did exactly what I was looking for.

Thanks again,

Lee.  Register To Reply