MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

1 1234567 8877662 TRUE
2 8877662 1000000 FALSE
3 2233445 2000000 FALSE
4 1155779 3000000 FALSE
5 1125678 4000000 FALSE


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


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.



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
and copying it down.

You could get the address or false by the formula
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