# 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'sIn column F & G I have another 1500 email address's in the two columns some are blanksI 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

#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 AIn I1 enter=IF(ISERROR(VLOOKUP(G1,\$A\$1:\$A\$2000,1,FALSE)),"","Y")and copy down to compare Col G to A

#2
December 21, 2012 at 15:25:00

 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.

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

 Hi DerbyDadGood 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.

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.

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

 Perfect Thanks