MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I compare columns and find matches?


Posted by Ben on August 01, 2001 9:26 AM

Hello, Please pardon this novice question. How do I find matches between two columns? I want to find the matches then have them put into a third column. What I have is a spread sheet that has several columns, there is a common text between the several of the columns each which contains part of the information I need. rather than manually find the information and match it up I am looking for a function to do this for me.

Thanks, Ben


Posted by Eric on August 01, 2001 9:53 AM

Ive seen this one posted a couple of times

Hi, try this
If you want to match values in column a to those in column b, then put the following in column c.
=if(isnumber(vlookup(b1,a$1:a$200,1,false))=true,"match","no match")
hope that helps

Posted by Ben on August 01, 2001 2:35 PM

Re: Ive seen this one posted a couple of times

How can I return the values to the 3rd column? The spread sheet I am referring to has several columns for instance Column "a" contains employees names column "b" contains employee ID column "c" contains employee name and column "d" contains employee phone number. Column "a" and "b" are matched up correctly as is column c and d. however columns a,b,c,d are not matched up. My goal is to parse through columns "a" and "c" which contain the same information then concatenate with data in columns "b" and "d" for all those that match into a new column "e". I hope this makes sense.

Thanks,
ben

Posted by Aladin Akyurek on August 01, 2001 2:52 PM

Re: Ive seen this one posted a couple of times

You mean they are not on the same row, I guess.

My goal is to parse through columns "a" and "c" which contain the same information then concatenate with data in columns "b" and "d" for all those that match into a new column "e". I hope this makes sense.

Dou you mean by concatenate stringing together all info related to an employee and placing in a cell in D?


Posted by Ben on August 01, 2001 3:11 PM

Re: Ive seen this one posted a couple of times

Correct the are not all in the same row. Lets say I have an employee John doe. In column a row 1 you would find "John Doe" in column b row 1 would be "Id 134". Column c row 12 "John Doe" column d row 12 "555-1212". I want to pull information into a new column e row 1 which would contain "John Doe ID 134 555-1212".


Posted by Aladin Akyurek on August 01, 2001 3:22 PM

Re: Ive seen this one posted a couple of times

In E1 enter: =A1&" "&VLOOKUP(A1,$A$1:$B$1000,2,0)&" "&VLOOKUP(A1,$C$1:$D$1000,2,0)

Adjust the ranges to your situation and copy down this as far as needed.

I'm assuming that the employee names are unique in A and in C.

Aladin

Posted by Ben on August 01, 2001 3:31 PM

Re: Ive seen this one posted a couple of times

You rock!

Posted by Ben on August 01, 2001 3:37 PM

Re: Ive seen this one posted a couple of times

You rock!