• Last Updated:
  • Dec 9th, 2008 9:00 am
Tags:
[OP]
Moderator
User avatar
Sep 30, 2001
29448 posts
8939 upvotes
Toronto

Excel help needed

Scenario:
I have two columns of names
I wish to compare Column B against Column A and report back those names which exist in Column A.

Example:
Column A
Abe
Andy
Barrie
Cameron
Cassie

Column B
Andy
Barrie
Claus


Result:
Andy and Barrie are returned back

I've done some searching and I haven't found exactly what I'm looking for. Conceptually I can see how it would be done in VB/VBA, but I would much rather grab something already made rather than wasting time coding it.

Thanks!
Be kind and civil with one another
6 replies
Sr. Member
User avatar
Dec 23, 2006
576 posts
188 upvotes
Have you tried the "Vlookup" in excel? It would let you do just that. It is difficult to explain without actual cell reference as an example. Try using Excel's help section and search for vlookup will give you some good samples.

hope this helps.
Deal Fanatic
Sep 29, 2005
6426 posts
1549 upvotes
Montreal
Vlookup will work. So will the Match function which will give you the row the match is found.

=vlookup(B1,A$1:A$5,false)
=MATCH(B1,A$1:A$5,0)
Phils
[OP]
Moderator
User avatar
Sep 30, 2001
29448 posts
8939 upvotes
Toronto
texedomel wrote: Have you tried the "Vlookup" in excel?
Phils wrote: Vlookup will work. So will the Match function which will give you the row the match is found.
I'll check out vlookup to see how it works, but I think the kb article posted may just do the trick.

Thanks!!
Be kind and civil with one another
Deal Addict
User avatar
Nov 24, 2002
2332 posts
175 upvotes
Ottawa
Phils wrote: Vlookup will work. So will the Match function which will give you the row the match is found.

=vlookup(B1,A$1:A$5,false)
=MATCH(B1,A$1:A$5,0)
Cool! I knew of the VLookUp formula, but never knew/used the Match formula. Could be very useful. Thanks.

~V79~
Sr. Member
Jan 20, 2007
777 posts
42 upvotes
Viz79 wrote: Cool! I knew of the VLookUp formula, but never knew/used the Match formula. Could be very useful. Thanks.

~V79~
Viz:

Match + Index are very powerful together.

For example, I want to know the province that has the 3rd largest population
(province names in a, population counts in b)

(this formula written by memory, might have typos):

=Index(A1:A10,match(B1:B10,LARGE(B1:B10,3),0)))

LARGE(B1:B10,3) -- finds the 3rd largest population
match(B1:B10,LARGE(B1:B10,3),0)) -- returns the position (a number between 1 and 10) in the array of the 3rd largest number
index(.. ) -- returns the names of the province with the 3rd largest population

Top