Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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:

    =VLOOKUP(A2,Blacklist!A:B,2,FALSE)

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
add comment

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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.