Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich

Use VLOOKUP.

Something like =IF(NOT(ISNA(VLOOKUP(....))),"GOOD","BAD")

For info on the VLOOKUP function:
http://www.techonthenet.com/excel/formulas/vlookup.php

Dave
--
Brevity is the soul of wit.


" wrote:

Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich

Can the VLOOKUP work across 2 different worksheets?

Dave F wrote:
Use VLOOKUP.

Something like =IF(NOT(ISNA(VLOOKUP(....))),"GOOD","BAD")

For info on the VLOOKUP function:
http://www.techonthenet.com/excel/formulas/vlookup.php

Dave
--
Brevity is the soul of wit.


" wrote:

Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich

Yes.
--
Brevity is the soul of wit.


" wrote:

Can the VLOOKUP work across 2 different worksheets?

Dave F wrote:
Use VLOOKUP.

Something like =IF(NOT(ISNA(VLOOKUP(....))),"GOOD","BAD")

For info on the VLOOKUP function:
http://www.techonthenet.com/excel/formulas/vlookup.php

Dave
--
Brevity is the soul of wit.


" wrote:

Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich

Ok so I'm not sure how to get it to work across different sheets so I
tried to just copy the data into 1 sheet and see if I can get the
VLOOKUP to work...this is what I came up with:

=IF(NOT(ISNA(VLOOKUP(A$1:A$7471,E$1:E$1307,1, FALSE))),"","Bad")

I put all the email addresses on record in ColumnA and the bad ones in
ColumnE but once I start pasting down, it doesn't flag all the matching
ones...it flags some but not all. I'm not sure what I'm doing wrong
here. please help.

Dave F wrote:
Yes.
--
Brevity is the soul of wit.


" wrote:

Can the VLOOKUP work across 2 different worksheets?

Dave F wrote:
Use VLOOKUP.

Something like =IF(NOT(ISNA(VLOOKUP(....))),"GOOD","BAD")

For info on the VLOOKUP function:
http://www.techonthenet.com/excel/formulas/vlookup.php

Dave
--
Brevity is the soul of wit.


" wrote:

Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich

I'm not sure I understand....you say the formula is flagging some bad emails
but not all bad emails?

Is it possible that some of the bad email addresses you have are not also in
your list of good and bad email addresses?
--
Brevity is the soul of wit.


" wrote:

Ok so I'm not sure how to get it to work across different sheets so I
tried to just copy the data into 1 sheet and see if I can get the
VLOOKUP to work...this is what I came up with:

=IF(NOT(ISNA(VLOOKUP(A$1:A$7471,E$1:E$1307,1, FALSE))),"","Bad")

I put all the email addresses on record in ColumnA and the bad ones in
ColumnE but once I start pasting down, it doesn't flag all the matching
ones...it flags some but not all. I'm not sure what I'm doing wrong
here. please help.

Dave F wrote:
Yes.
--
Brevity is the soul of wit.


" wrote:

Can the VLOOKUP work across 2 different worksheets?

Dave F wrote:
Use VLOOKUP.

Something like =IF(NOT(ISNA(VLOOKUP(....))),"GOOD","BAD")

For info on the VLOOKUP function:
http://www.techonthenet.com/excel/formulas/vlookup.php

Dave
--
Brevity is the soul of wit.


" wrote:

Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich

I know it's weird but here's an example using that formula below.

Column A Column E
BAD


BAD


I did the above as a test to check the formula and see if it is
correct, it should flag both but it doesn't, just the first one and in
the 3rd row, it flags an address which doesn't exist in column 3 as
bad. I'm not sure what's wrong with my formula that's causing this.


Dave F wrote:
I'm not sure I understand....you say the formula is flagging some bad emails
but not all bad emails?

Is it possible that some of the bad email addresses you have are not also in
your list of good and bad email addresses?
--
Brevity is the soul of wit.


" wrote:

Ok so I'm not sure how to get it to work across different sheets so I
tried to just copy the data into 1 sheet and see if I can get the
VLOOKUP to work...this is what I came up with:

=IF(NOT(ISNA(VLOOKUP(A$1:A$7471,E$1:E$1307,1, FALSE))),"","Bad")

I put all the email addresses on record in ColumnA and the bad ones in
ColumnE but once I start pasting down, it doesn't flag all the matching
ones...it flags some but not all. I'm not sure what I'm doing wrong
here. please help.

Dave F wrote:
Yes.
--
Brevity is the soul of wit.


" wrote:

Can the VLOOKUP work across 2 different worksheets?

Dave F wrote:
Use VLOOKUP.

Something like =IF(NOT(ISNA(VLOOKUP(....))),"GOOD","BAD")

For info on the VLOOKUP function:
http://www.techonthenet.com/excel/formulas/vlookup.php

Dave
--
Brevity is the soul of wit.


" wrote:

Problem:

column1 of worksheet1 contains a list of bad email addresses

column1 of worksheet2 contains a list of all email addresses on file

i need to compare the list of bad addresses (worksheet1) to the list of
all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column
of the all addresses list (worksheet2).

i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.

p.s. the all email address list (worksheet2) can be quite long...around
8000 rows.

Rich