Share via

Vlookup to find matching email addresses

Anonymous
2011-10-12T23:45:33+00:00

I have a list of registrants (col A) and list of attendees (col B), and want to find matching registrants that attended an event. The are email addresses and are sorted ascending. I tried the following formula but in the third column I get #REF!. I am not sure what this means. I tried TRUE, but got the same result.

=VLOOKUP(B3,$A$2:$A$70,3,FALSE)

Ideally, I like to be able to highlight in color in the list of registrants that are attendees. How do I incorporate highlighting in the formula? 

Amy

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-10-13T16:07:00+00:00

I'm a little bit confused here.  

The #REF! error is due to you're asking VLOOKUP to return the value in the third column of $A$2:$A$70, which obviously doesn't exist.

If you just want a highlight color, select your registrants and chose Format/Conditional Formatting. Add a classic rule (Use a formula to determine which cells to format) and enter (assuming A2 is the active cell)

=COUNTIF(B:B,A2)>0

and assign a format to it.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-10-13T23:53:58+00:00

    Ah, I see. I misunderstood the meaning of the column index number. The format/conditional formatting is a nice feature. I was able to use this function instead of the vlookup, to find duplicates. 

    Thanks!

    Amy

    Was this answer helpful?

    0 comments