Compare words in a certain column from another column and get percentage

Good Day!

I am newbie here and would like to ask for your assistance in this issue.

I have two columns with the following requirements:

1. descriptions in Column A will be compared to all available data in Column B and should reflect the description (in column d) with closest match and with highest percentage (in column c)
2. should not be case sensitive

E.g

column a__column b_column c_column d
Dog 123____ abc ____ 50%____dog
cat (abc)____dog___ 40%____ abc
mouse____ xxx ____ 0%____ n/a


as you noticed, for column a (A2) which is dog 123, it was matched with all the data in column B. C1 reflected 50% , the highest percentage match and also reflect in D1 the description of cell in column b with highest match(b3)

Hope you can assist me.

Thank you!

Hello homer2211 and Welcome to Excel Forum.
Assuming that columns A:B are manually input:
Column C could be populated using: =IFERROR(LEN(INDEX(B$2:B$4,AGGREGATE(15,6,ROW(A$2:A$4)/(SEARCH(B2,A$2:A$4)>0),1)))/LEN(SUBSTITUTE(A2," ","")),0)
Column D could be populated using: =IF(C2=0,"n/a",INDEX(B$1:B$3,AGGREGATE(15,6,ROW(A$2:A$4)/(SEARCH(B2,A$2:A$4)>0),1)))
In the future please utilize the instructions in the banner at the top of the page to attach an .xlsx file rather than expecting us to copy/type data from a post into a spreadsheet.
Let us know if you have any questions.