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

  • Thread starter Thread starter ModelerGirl
  • Start date Start date
M

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
 
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)

---
 
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
 
Back
Top