How to search for a value in a range of cells as a Function

Posted by Mark on October 09, 2001 9:33 AM

Is there a function to search a range of cells for a text value and return TRUE or FALSE in an adjoining column?

I have Col A with 500 values in A1:A500. I want to search for the value that equals the one in B1 (using a formula/function in C1). If it matches, then return TRUE, is not return FALSE or blank in C1.

It'd be even better if I knew the cell reference where the search returned true.

Example:
A B C
1 1234567 8877662 TRUE
2 8877662 1000000 FALSE
3 2233445 2000000 FALSE
4 1155779 3000000 FALSE
5 1125678 4000000 FALSE

Thanks!

Posted by Aladin Akyurek on October 09, 2001 9:41 AM

Mark,

In C1 enter: =IF(COUNTIF($A$1:$A$500,B1),ADDRESS(MATCH(B1,$A$1:$A$500,0),1),"Not Found")

Copy down this as needed.

Aladin

==========

Posted by IML on October 09, 2001 9:45 AM

I'm not sure I follow, but you could get your true false by using
=B1=$B$1
and copying it down.

You could get the address or false by the formula
=IF(B1=$B$1,ADDRESS(ROW(),2))
and copying it down.

Sorry if I'm missing this entirely.

Posted by . on October 09, 2001 10:01 AM

in c1 type =$b$1=a1 and copy down

Posted by IML on October 09, 2001 10:11 AM

Re: in c1 type =$b$1=a1 and copy down

I'm not sure who added this, but if this more what you were after, the second formula would be modified to
=IF(A1=$B$1,ADDRESS(ROW(),1))