Find the position of a related string in a list-CodePudding

In my worksheet, B2:F2 and B8:D8 are already filled in.

I would like to find the formulas for B9:D9. For instance, B9 is 2 because we can find Lee in Lee XXXX and Lee is at the second place of B2:F2. C9 is 4 because we can find Jim in Jim XXXX and Jim is at the second place of B2:F2.

To check the relation of Lee and Lee XXXX (Jim and Jim XXXX), we could use SEARCH, left_substring (if such a function exists).

Both single formulas or array formula will be fine. Using LAMBDA function is secondary choice, because it is still in preview.

Could anyone help?

CodePudding user response：

If what is needed is always the first "word":

=MATCH(LEFT(B8,FIND(" ",B8)-1),$B$2:$F$2,0)

If you want to search on any position in the string:

=AGGREGATE(15,7,(COLUMN($B$2:$F$2)-MIN(COLUMN($B$2:$F$2)) 1)/(ISNUMBER(SEARCH(" "&$B$2:$F$2&" "," "&B8&" "))),1)

With FILTER instead of AGGREGATE:

=@FILTER((COLUMN($B$2:$F$2)-MIN(COLUMN($B$2:$F$2)) 1),ISNUMBER(SEARCH(" "&$B$2:$F$2&" "," "&B8&" ")),"")