Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two worksheets, one containing a list of e-mail addresses and information, the other containing e-mail addresses that should be blacklisted. To remove the blacklisted e-mail addresses I thought it would be handy to add a column in the first worksheet which indicates whether or not the e-mail address in that row can be found in the blacklist worksheet.

I tried using VLookup, Match and Search but none are giving what I want...

What am I doing wrong here?

@Peter: Using IF and ISERROR still doesn't work...

enter image description here

enter image description here

Vlookup second row: =VLOOKUP(A2;Blacklist!A:B;2)

Match second row: =MATCH(A2;Blacklist!A:B)

Search second row: =SEARCH(A2;Blacklist!A:B)

Worksheet enter image description here

Blacklist Blacklist

@Peter Albert: adding 0 parameter

enter image description here

enter image description here

share|improve this question
up vote 1 down vote accepted

You're getting "weird" results with your VLOOKUP formula because in your source data range (Blacklist!A:B), the values you're trying to retrieve are located to the left of the values you're trying to look up.

To fix this:

  1. Switch the Source & Pattern columns in the Blacklist worksheet, like so:

    enter image description here

  2. Don't forget to add VLOOKUP's 4th parameter. Your formula should be:


If your ultimate goal is just to determine which email addresses are in the blacklist, you an use this formula:

=IF(COUNTIF(Blacklist!B:B,A2), "Blacklisted", "Not Blacklisted")

Where Blacklist!B:B is the column that contains the blacklisted email address

share|improve this answer
Thanks this worked! – ropstah Mar 20 '13 at 13:25

VLOOKUP and MATCH have another parameter, that is either TRUE or FALSE. TRUE is the default and results in wrong results if the data to be searched is not sorted! Therefore, simply add FALSE (or 0 as a short form) to your formulas and it'll work!

for your case, I'd use the MATCH function to determine if there is any match. Match return #N/A if it can't find a match or the number where it matches - so combining it with ISERROR will give you the best result:

=IF(ISERROR(MATCH(A2;Blacklist!B:B;0));"Not blacklisted";"Blacklisted!")
share|improve this answer
This still doesn't work... I've attached screenshots – ropstah Mar 20 '13 at 10:01
@ropstah: See the updated answer! – Peter Albert Mar 20 '13 at 11:24
Call me stupid, but it just doesn't find the value... All come up as "Not blacklisted". How is this possible? – ropstah Mar 20 '13 at 11:37
I've attached new screenshots... everything is exactly as you specify... – ropstah Mar 20 '13 at 11:37
did you copy the formula? I changed A:B to B:B (see Kaze's answer), so make sure that this is used. if that does not work, check if the email addresses in the Blacklist sheet have some spaces at the end. – Peter Albert Mar 20 '13 at 13:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .