May 17 2020 01:08 AM
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
May 17 2020 01:19 AM - edited May 17 2020 01:21 AM
@Rising Flight This could be, e.g. in C1
=IFERROR(VLOOKUP(B1,A:A,2,FALSE),"Not Found")
and copy it dow column B.
May 17 2020 01:58 AM
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
May 17 2020 02:53 AM - edited May 17 2020 02:53 AM
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.
May 17 2020 02:53 AM - edited May 17 2020 02:53 AM
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.