# 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

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.

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.

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

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

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

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

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?

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

Attachment 682330

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

Originally Posted by Metoo7
Attachment 682330
Sorry but it says 'invalid attachment'.

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

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

``Please Login or Register  to view this content.``

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

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1