SOLVED

compare two columns

Brass Contributor

Hi All

i am new to excel, i have two columns, in column A i have 500 records, in Column B i have 200 records. i want to compare Column B with Column A and pull the matching records in Column C. When i go to 

Conditional Formatting-->Format only unique or duplicate value, but i dont see option to pull the matching data to column C 

 

3 Replies

@Rising Flight This could be, e.g. in C1

 

=IFERROR(VLOOKUP(B1,A:A,2,FALSE),"Not Found")

 

and copy it dow column B.

Thanks Riny

In the below screenshot i have ColumnA and ColumnB and in Column C i only want to get duplicate records, so the output i want to get in Column C is SrvC,SrvB,SrvA,SrvF since these 4 records in Column B are matching with Column A

col.JPG

best response confirmed by Rising Flight (Brass Contributor)
Solution

@Rising Flight See now that I made a mistake in the formula. Sorry! It should have been:

 

=IFERROR(VLOOKUP(B1,A:A,1,FALSE),"Not Found")

 

But, since you are not interested to see the "Not Found", you could consider to filter these out. A manual process you probable don't want either.

This made me re-think and I could come up with the following (not most elegant) solution:

 This formula works only if your Excel version supports the relatively new FILTER function. 

 

=FILTER(VLOOKUP(B1:B5,A:A,1,FALSE),NOT(ISNA(VLOOKUP(B1:B5,A:A,1,FALSE))))

 

It's demonstrated in the attached workbook. 

1 best response

Accepted Solutions
best response confirmed by Rising Flight (Brass Contributor)
Solution

@Rising Flight See now that I made a mistake in the formula. Sorry! It should have been:

 

=IFERROR(VLOOKUP(B1,A:A,1,FALSE),"Not Found")

 

But, since you are not interested to see the "Not Found", you could consider to filter these out. A manual process you probable don't want either.

This made me re-think and I could come up with the following (not most elegant) solution:

 This formula works only if your Excel version supports the relatively new FILTER function. 

 

=FILTER(VLOOKUP(B1:B5,A:A,1,FALSE),NOT(ISNA(VLOOKUP(B1:B5,A:A,1,FALSE))))

 

It's demonstrated in the attached workbook. 

View solution in original post