Link to home
Start Free TrialLog in
Avatar of John H
John HFlag for United States of America

asked on

Excel - Count of value matches between 2 columns?

Ok, here goes:

I have a long list of words in column A.  I have short list of words in column B.

I want to know the count of exact or similar matches of a word in column B that appears in column A.

simple for you guys, but i am stumped :)

Thanks!
Screen-Shot-2017-05-04-at-2.47.06-AM.png
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

in Cell C2, apply formula:

=COUNTIF($A:$A,B2)

Open in new window


drag this formula down for the cells below it.
sample as attached.
29020395.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ah, didn't see the requirement of "or similar matches" which not really obvious from the image attached.
=COUNTIF($A$2:$A$8,"*"&B2&"*") will also work, but you have to define the range of cells for your lookup.

This also assumes that by "similar words", you mean a shorter word within a longer word. If a word matches two different searches, do you want it counted twice? Or is it not possible for that to happen? (e.g. "bookworm" would be counted as "book" and "worm")
Avatar of John H

ASKER

Sorry for not including a "similar" example in the screenshot

Thanks Shums, doubtful I would have ever figured out a formula like that - it works, huge timesaver :)
Pleased to help John!