How to use an find best match based on the wording within the same cell

Question

I am working on Google sheet, In this I need find the best match based on the word in this sheet.

Our most of the basic Look up function like Vlookup, Hlookup, Xlookup or Index-match only support for Look the whole wording.

Then I used Split function to {"=SPLIT(E3," ")"}, Split all word in 2nd Table.

Then I wrote formula Using LET, HSTACK, BYROW, TOROW , CHOOSEROWS and Other formula. Below is the formula

=LET(a,HSTACK($D$2:$D$5, BYROW(IFERROR(ARRAYFORMULA(XMATCH($F$2:$H$5,SPLIT(A2," "))),0),LAMBDA(x,SUM(x)))),TOROW(CHOOSEROWS(INDEX(SORT(FILTER(a,INDEX(a,,2)<>0),2,1),,1),1)))

But I think I need to do some correction on this, because this not give the 100% accuracy.

Answer 1

Yes you can achieve following result with help of "Filter , IsNumber & Search Function" .