Finding Commonalities b/w 2 Columns

Hello,

I am attempting to create a formula that can tell me what percentage of text in Column B is the same as text in Column A.

As an example (another example is in the attachment), if A1:A3 contained A, B, C, and B1:B4 contained B, C, D, E, then through the one or two formulas necessary, Excel would tell me that B and C are the commonalities and give me a 66.7% return (A&B commonalities divided by total A).

Thank you for any suggestions/solutions!

Albert

Perhaps:

=SUM(COUNTIF(A1:A7,B1:B6))/COUNTA(A1:A7)

confirmed with CTRL+SHIFT+ENTER not just ENTER.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save