Hi, can anyone help me on this? Let say we have 3 columns of data. I want to
show in another column for data that appears in all 3 columns. E.g.
A B C
1 apple orange education
2 education education card
3 flower apple flower
4 baby phone baby
5 phone baby apple
I want to return "apple", "education" and "baby" in another column since
these 3 terms appear in all 3 columns.

Is there a way that I can do it quickly? Thanks in advance for your help!!

On Jun 1, 12:31 pm, Landa wrote:
Hi, can anyone help me on this? Let say we have 3 columns of data. I want to
show in another column for data that appears in all 3 columns. E.g.
A B C
1 apple orange education
2 education education card
3 flower apple flower
4 baby phone baby
5 phone baby apple
I want to return "apple", "education" and "baby" in another column since
these 3 terms appear in all 3 columns.

Is there a way that I can do it quickly? Thanks in advance for your help!!

If you start your intersect column from D1 then use the following
*Array* formula in D1 and copy down.

=INDEX($A$1:$A$5,SMALL(IF((COUNTIF($B$1:$B$5,$A$1: $A$5)0)*(COUNTIF($C
$1:$C$5,$A$1:$A$5)0),ROW($A$1:$A$5)-ROW($A$1)+1),ROW()-ROW($D$1)+1))

- Change $D$1 to whatever cell you start your output from.
- This is an *array* formula. Commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

Thanks a lot Kostis. It works perfectly :)

"vezerid" wrote:

On Jun 1, 12:31 pm, Landa wrote:
Hi, can anyone help me on this? Let say we have 3 columns of data. I want to
show in another column for data that appears in all 3 columns. E.g.
A B C
1 apple orange education
2 education education card
3 flower apple flower
4 baby phone baby
5 phone baby apple
I want to return "apple", "education" and "baby" in another column since
these 3 terms appear in all 3 columns.

Is there a way that I can do it quickly? Thanks in advance for your help!!

If you start your intersect column from D1 then use the following
*Array* formula in D1 and copy down.

=INDEX($A$1:$A$5,SMALL(IF((COUNTIF($B$1:$B$5,$A$1: $A$5)0)*(COUNTIF($C
$1:$C$5,$A$1:$A$5)0),ROW($A$1:$A$5)-ROW($A$1)+1),ROW()-ROW($D$1)+1))

- Change $D$1 to whatever cell you start your output from.
- This is an *array* formula. Commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

Glad it worked. Thanks for the feedback.

Kostis

On Jun 7, 12:41 pm, Landa wrote:
Thanks a lot Kostis. It works perfectly :)

"vezerid" wrote:
On Jun 1, 12:31 pm, Landa wrote:
Hi, can anyone help me on this? Let say we have 3 columns of data. I want to
show in another column for data that appears in all 3 columns. E.g.
A B C
1 apple orange education
2 education education card
3 flower apple flower
4 baby phone baby
5 phone baby apple
I want to return "apple", "education" and "baby" in another column since
these 3 terms appear in all 3 columns.

Is there a way that I can do it quickly? Thanks in advance for your help!!

If you start your intersect column from D1 then use the following
*Array* formula in D1 and copy down.

=INDEX($A$1:$A$5,SMALL(IF((COUNTIF($B$1:$B$5,$A$1: $A$5)0)*(COUNTIF($C
$1:$C$5,$A$1:$A$5)0),ROW($A$1:$A$5)-ROW($A$1)+1),ROW()-ROW($D$1)+1))

- Change $D$1 to whatever cell you start your output from.
- This is an *array* formula. Commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides