Comparing two columns & showing identical values

Originally Posted by Sham Sundar

I have two columns. One column has values for 2600 rows and the other has values in 1000 row. I need to compare these two columns & the values appearing in both the columns are to be shown in third column.

Hi,

Please find the attached file.

This is using a helper column.

Assuming the two columns with values are in A and B, use the formula for helper column :

Formula:

=IF(COUNTIF(A:A,B2)>0,MAX($C$1:C1)+1,"")

And then to get the matching values column, use the formula:

Formula:

=IFERROR(INDEX(A:A,MATCH(ROW(2:2)-1,C:C,0)),"")

Assuming helper column is C


Regards