Checking if a cell value in one range is contained in a second range?

[ModelerGirl]

Hi all,

I am trying to check whether cell values in one range (say A1:A10) are
contained in a second range (say A20:A30). I tried the formula

countif(A20:A30,A[row number])=1

which was supposed to return "TRUE" is the cell value in that row WAS
contained in the second range and "FALSE" otherwise.

But I sanity checked this, and it seemed to yield the wrong answer some
of the time. Could someone suggest a different formula that would
work, or correct this one?

Thanks so much for your help!

Kate

 

[Bob Phillips]

It should be

=countif($A$20:$A$30,A1)=1

how did it not work, and on what data?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ModelerGirl" <[email protected]>
wrote in message
news:[email protected]...

 

[Max]

Another option to play with is:
=SUMPRODUCT(--(TRIM($A$20:$A$30)=TRIM(A1)))>0
(source range may have extraneous spaces throwing the matching off)

---

 

[ModelerGirl]

Thanks, Max. This formula seemed to work!

Bob,

I do think that the formula you gave me a few weeks ago was correct
but that there were extra spaces interfering

 

[Max]

You're welcome !
Thanks for the feedback ..