Compare Two columns, find a partial match and return occurrences

Hi, I really need help with this formula - I've tried several things and none of them have worked.

I have Two columns A and B. They both have Headers, so the names begin from row 2 onwards....

I need to see if the name in A2 appears anywhere in column B, and if it does, I need a yes/no (or better yet, would it be possible to say it appears x number of times, for example?)

Another thing, I need to know if the entire name in A2, appears anywhere in column B, but it doesn't have to be an exact match. So if I'm looking for "car" in column B, I need it to match "car", "cartoon", "supercar" etc.

Thanks so much!!!

Array enter (Ctrl+Shift+Enter)...

Formula:

Please Login or Register  to view this content.

...where ? is the extent of column B. Then copy or drag down.

Thanks for the response. I'm not sure I understand the array part...? I'm using excel on a mac. I tried using ctrl+shift+enter and nothing happens? Do I have to select something first? I pasted the formula in C2 but it just says 0 when I drag down.

Copy formula in my post. Put cell C2 (or any row 2 cell of a blank column) in edit mode (either in cell or in formula bar). Paste. Replace 2x? as directed. (Mac) Press ^+↑+Return.

Sorry, I wasn't aware Excel Mac had different array formula entry confirmation.

Array formula:
Mac entry confirmation... https://exceljet.net/keyboard-shortc...-array-formula
General info... https://www.google.com/search?q=excel+array+formula

Thanks for your response, but unfortunately I still wasn't able to get it work. But I found another way, I just used the Countif formula along with &"*" wildcard to find matches and get a total count of the number of matches. But I did learn something new about working with arrays, so thanks again!