Tried everything please help In column A I have a list of 1500 email address's

In column F & G I have another 1500 email address's in the two columns some are blanks

I want to know if any email adderess in either column F or G matches an email address in column A return "Y"

Many thanks

In H1 enter this formula =IF(ISERROR(VLOOKUP(F1,$A$1:$A$2000,1,FALSE)),"","Y")

and copy it to the end of the list - this will compare column F to Col A

In I1 enter

=IF(ISERROR(VLOOKUP(G1,$A$1:$A$2000,1,FALSE)),"","Y")

and copy down to compare Col G to A

I would suggest using IF(ISNA(... instead of IF(ISERROR(... Using ISERROR can mask other errors, such as #REF, while #N/A is the error we are actually looking for if there isn't a match.

For example, let's say that the value in F1 can be found in A1:A2000. We should expect a Y, correct?

Now, let's say I make a typo when entering the formula and use a col_num argument of 2 when there is only 1 column in A1:A2000.

=IF(ISERROR(VLOOKUP(F1,$A$1:$A$6,

2,0)),"","Y")The VLOOKUP will return a #REF error and the ISERROR will be TRUE so the formula will return "" even though the value in F1 was found.

However, if I use

=IF(ISNA(VLOOKUP(F1,$A$1:$A$6,2,0)),"","Y")

I will get a Y since the ISNA is looking for a specific error (#N/A) which will be returned if the value in F1 is not found.

BTW...I prefer to use

0instead of FALSE just to save keystrokes.Another option is to use the following formula which will return F or G so not only will you know if the email address is found, but you'll know which column it was found in.

Drag this down to the bottom of your list in Column A

=IF(AND(ISNA(VLOOKUP(A1,$F$1:$F$2000,1,0)),

ISNA(VLOOKUP(A1,$G$1:$G$2000,1,0))),"",

IF(ISNA(VLOOKUP(A1,$F$1:$F$2000,1,0)),"G","F"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad Good point with ISNA vs ISERROR - I will note that for future use.

Using "0" instead of "False"? I don't mind the extra keystrokes.

Your solution to the OP's question - VERY clever. Wish I had thought of it.

Thanks again for the tips. Always welcome and much appreciated.

Whenever there is a TRUE or FALSE argument required in a formula, you can use 1 or 0 instead. Personal preference, but I prefer the digits instead of the letters.

If fact, if you want to save even more keystrokes, you can omit the last argument.

If you use =VLOOKUP(A1,$B$1:$C$10,2) Excel will default the range_lookup argument to be 1 (TRUE)

Add the comma, and Excel will interpret the nothingness after it to be 0 (FALSE):

=VLOOKUP(A1,$B$1:$C$10,2,)

Of course, using those syntaxes can be confusing to others who may not be aware of those shortcuts.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Perfect Thanks

Really Helpful Thanks I got loads of errors in my Vlookup even though there was no error which at this stage I dont understand why.So reading through your explanation I wasnt using VLookup correctly

Ask Your Question

Weekly Poll

Do you think the passwords you use for websites are strong enough?

Discuss in The Lounge

Poll History