John H
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
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
sample as attached.
29020395.xlsx
29020395.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
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")
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 :)
Thanks Shums, doubtful I would have ever figured out a formula like that - it works, huge timesaver :)
Pleased to help John!
Open in new window
drag this formula down for the cells below it.