Articles

Solved Check to see if an email address is on the database

December 21, 2012 at 04:13:06
Specs: Windows 7

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


See More: Check to see if an email address is on the database

Report •


#1
December 21, 2012 at 09:14:36

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


Report •

#2
December 21, 2012 at 15:25:00
✔ Best Answer

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 0 instead 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.


Report •

#3
December 21, 2012 at 16:15:01

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.


Report •

Related Solutions

#4
December 21, 2012 at 20:51:45

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.


Report •

#5
December 22, 2012 at 09:31:08

Perfect Thanks

Report •

#6
December 22, 2012 at 09:42:34

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


Report •


Ask Question