Comparing two columns & showing identical values

[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.

[pjwhitfield]

Assuming columns are A - C and theres a header row

In C2 enter the following

Please Login or Register  to view this content.

And then drag down

[chullan88]

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 :

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Assuming helper column is C


Regards

[samba_ravi]

pls attach a sample file

[shukla.ankur281190]

Assuming A & B column have data then.

Try

D2

Please Login or Register  to view this content.

with control+Shift+Enter. and drag down.

Check the attached file.

[samba_ravi]

or can try

Please Login or Register  to view this content.

change range reference as per your needs

[Tony Valko]

Try this...

Data Range

	A	B	C	D
1	1	1		1
2	2	4		4
3	3	5		5
4	4	8		8
5	5	12		13
6	6	13
7	7	30
8	8	32
9	9	40
10	11
11	13
12	15
13	17
14	20
15	25
16	------	------	------	------

This array formula** entered in D1:

=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(MATCH(B$1:B$9,A$1:A$15,0)),ROW(B$1:B$9)),ROWS(D$1:D1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.