Need formula for comparing items in one column to another.

[bird333]

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

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

[bird333]

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.

Added attachment.

[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

[bird333]

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

[Metoo7]

G4

Please Login or Register  to view this content.

[bird333]

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

Thanks! This works. Do you mind explaining how this works?

[Metoo7]

Please see my screenshot explanation:
Attachment 682330

[bird333]

Please see my screenshot explanation:
Attachment 682330

Sorry but it says 'invalid attachment'.

[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

[Metoo7]

how about now:

Please Login or Register  to view this content.

[bird333]

=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

Thanks for the reply.

[bird333]

how about now:

Please Login or Register  to view this content.

Yes I can see that now. Thanks again.

[bird333]

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.