How can I match words from one column with another?

[cwinhall]

In column A I have a list of 500 words.

In column B I have a list of 300 words.

In column C I want to list all the words that column A and B have in common. As seen in the example below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]elephant[/TD]
[TD]crocodile[/TD]
[TD]elephant[/TD]
[/TR]
[TR]
[TD]rat[/TD]
[TD]mosquito[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]circus[/TD]
[TD]elephant[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!

 

[cwinhall]

I would also like to know how to do it for several more columns. Not just matching words between 2 columns, but 5, even 10. How does that change the formula?

Look forward to the responses!

 

[Peter_SSs]

In column C I want to list all the words that column A and B have in common.

Try this, copied down

Excel Workbook
	A	B	C
1	List 1	List 2	Common
2	elephant	crocodile	elephant
3	rat	mosquito
4	circus	elephant
5
Common Words

I would also like to know how to do it for several more columns. Not just matching words between 2 columns, but 5, even 10. How does that change the formula?

Not sure I understand exactly what you want. Could we see sample data and expected results for that scenario too (lets say just 4 columns)?

 

[cwinhall]

Thank you! I got it working!

 

[Peter_SSs]

Thank you! I got it working!

You're welcome.

 

[Joe4]

Cross-posted here: https://www.excelforum.com/excel-fo...rds-from-one-column-with-several-othersr.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered. Please be sure to do this in the future.

Thank you.