# Need formula for comparing items in one column to another.

1. ## Need formula for comparing items in one column to another.

I need a formula that will tell me if every item in one column range is somewhere in a second column. The following formula tells me if one item (A100 in this case) is in a column. =SUMPRODUCT(--(\$A100=\$A\$10:\$A99))>0 I tried to do something like =SUMPRODUCT(--(\$A150:\$A170=\$A\$10:\$A99))>0 but that doesn't work. TIA  Register To Reply

2. ## Re: Need formula for comparing items in one column to another.

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.  Register To Reply

3. ## Re: Need formula for comparing items in one column to another. Originally Posted by CARACALLA Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.  Register To Reply

4. ## Re: Need formula for comparing items in one column to another.

B2=IF(ISNUMBER(MATCH(A2,\$D\$2:\$D\$6,0)),"match ","no match ")

Copy down

C2=IFERROR(MATCH(A2,\$D\$2:\$D\$6,0),"no match")

copy down  Register To Reply

5. ## Re: Need formula for comparing items in one column to another. Originally Posted by CARACALLA B2=IF(ISNUMBER(MATCH(A2,\$D\$2:\$D\$6,0)),"match ","no match ")

Copy down

C2=IFERROR(MATCH(A2,\$D\$2:\$D\$6,0),"no match")

copy down
Perhaps I wasn't clear. I can do that with my original formula (i.e. copy it down the spreadsheet). I want one formula in one cell that examines the ranges and spits out a True or False (really I want a 1 or 0).  Register To Reply

6. ## Re: Need formula for comparing items in one column to another.

G4 ``Please Login or Register  to view this content.``  Register To Reply

7. ## Re: Need formula for comparing items in one column to another. Originally Posted by Metoo7 G4
=sumproduct(--(countif(d2:d6,a2:a4)=0))=0
Thanks! This works. Do you mind explaining how this works?  Register To Reply

8. ## Re: Need formula for comparing items in one column to another.

Attachment 682330  Register To Reply

9. ## Re: Need formula for comparing items in one column to another. Originally Posted by Metoo7 Attachment 682330
Sorry but it says 'invalid attachment'.  Register To Reply

10. ## Re: Need formula for comparing items in one column to another.

=sumproduct(--(countif(d2:d6,a2:a4)=0))=0

Search (by counting) each value in cell A2,A3,A4 in range D2:D6, if found, count 1
if any cell found, sumproduct>0=>FALSE
if nothing found, sumproduct=0=>TRUE  Register To Reply

11. ## Re: Need formula for comparing items in one column to another. ``Please Login or Register  to view this content.``  Register To Reply

12. ## Re: Need formula for comparing items in one column to another. Originally Posted by bebo021999 =sumproduct(--(countif(d2:d6,a2:a4)=0))=0

Search (by counting) each value in cell A2,A3,A4 in range D2:D6, if found, count 1
if any cell found, sumproduct>0=>FALSE
if nothing found, sumproduct=0=>TRUE  Register To Reply

13. ## Re: Need formula for comparing items in one column to another. Originally Posted by Metoo7  ``Please Login or Register  to view this content.``
Yes I can see that now. Thanks again.  Register To Reply

14. ## Re: Need formula for comparing items in one column to another.

Is there a way to tweak this formula to ignore blank cells in the 'a2:a4' range? Or at least make them '1' so they are checked if they are 0 (of course they wouldn't be)? This formula fails even when there is a blank cell in the "d2:d6" range that should match the blank cell in the "a2:a4" range.  Register To Reply