Hi

I have 3 columns, A, B and C. A and B contain email addresses, and I
want to set C to "Y" if the email address in A is also in B.

A has thousands of entries, B has hundreds, so its not a simple matter
of sorting them and manually setting C. Where the email addresses
match, they will match exactly (they're from the same source table).

Spreadsheet formulae are not my forte, and the help on lookup hasn't
really helped.

Any ideas?


Shaun

try in C1
=if(countif(B:B,A1)0,"Y","")
and copy down



"shauny" wrote:

Hi

I have 3 columns, A, B and C. A and B contain email addresses, and I
want to set C to "Y" if the email address in A is also in B.

A has thousands of entries, B has hundreds, so its not a simple matter
of sorting them and manually setting C. Where the email addresses
match, they will match exactly (they're from the same source table).

Spreadsheet formulae are not my forte, and the help on lookup hasn't
really helped.

Any ideas?


Shaun

In first row where there are email addresses in A and B, put this in C
=IF(COUNTIF(A$1:A$9,B1)0,"Y","")
change the A$1:A$9 to reference all the cells used in column A. Fill the
formula to the end of the list in column B.

That will show any that are in both lists, whether or not they are on the
same row.

"shauny" wrote:

Hi

I have 3 columns, A, B and C. A and B contain email addresses, and I
want to set C to "Y" if the email address in A is also in B.

A has thousands of entries, B has hundreds, so its not a simple matter
of sorting them and manually setting C. Where the email addresses
match, they will match exactly (they're from the same source table).

Spreadsheet formulae are not my forte, and the help on lookup hasn't
really helped.

Any ideas?


Shaun

One way:

C1: =IF(COUNTIF(B:B,A1),"Y","")

In article .com,
shauny wrote:

Hi

I have 3 columns, A, B and C. A and B contain email addresses, and I
want to set C to "Y" if the email address in A is also in B.

A has thousands of entries, B has hundreds, so its not a simple matter
of sorting them and manually setting C. Where the email addresses
match, they will match exactly (they're from the same source table).

Spreadsheet formulae are not my forte, and the help on lookup hasn't
really helped.

Any ideas?


Shaun

Shaun,

This formula will tell you how many matching entries for A2 exist anywhere in column B:
COUNTIF(B:B,A2)

This will put "y" in the cell
=IF(COUNTIF(B:B,A2),"y","")

Since there are more entries in column A than in B, I don't know where you want to put the
formula. Should it match B against A (the opposite of above)? If so.

=IF(COUNTIF(A:A,B2),"y","")

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"shauny" wrote in message
oups.com...
Hi

I have 3 columns, A, B and C. A and B contain email addresses, and I
want to set C to "Y" if the email address in A is also in B.

A has thousands of entries, B has hundreds, so its not a simple matter
of sorting them and manually setting C. Where the email addresses
match, they will match exactly (they're from the same source table).

Spreadsheet formulae are not my forte, and the help on lookup hasn't
really helped.

Any ideas?


Shaun

On 5 Jun, 17:18, "Earl Kiosterud" wrote:
Shaun,

This formula will tell you how many matching entries for A2 exist anywhere in column B:
COUNTIF(B:B,A2)

This will put "y" in the cell
=IF(COUNTIF(B:B,A2),"y","")

Since there are more entries in column A than in B, I don't know where you want to put the
formula. Should it match B against A (the opposite of above)? If so.

=IF(COUNTIF(A:A,B2),"y","")

--
Earl Kiosterudwww.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------"shauny" wrote in message

oups.com...

Hi

I have 3 columns, A, B and C. A and B contain email addresses, and I
want to set C to "Y" if the email address in A is also in B.

A has thousands of entries, B has hundreds, so its not a simple matter
of sorting them and manually setting C. Where the email addresses
match, they will match exactly (they're from the same source table).

Spreadsheet formulae are not my forte, and the help on lookup hasn't
really helped.

Any ideas?

Shaun

Hi

Excellent - many thanks people!

Regards
Shaun