XLOOKUP or VLOOKUP to find specific word in array cell with multiple words in it, and how?

[xdriver]

I have a list that contains the lookup values such as "Jo" and an array that has values in single cells such as "John Doe" and a different row such as "Jo Doe" what method and settings could I use to find the cells containing "Jo Doe" and not "John Doe"? Do i need to split the array name cells to make this work, since my current XLOOKUP will return both the "Jo Doe" and the "John Doe"

 

[xdriver]

I haven't gone through all of them yet, but this looks amazing and I appreciate this help. Would it be possible to explain how you created this/what each part of the equation is doing? Thank you so much.

 

[Peter_SSs]

Would it be possible to explain how you created this/what each part of the equation is doing?

=LET(n,FILTER(A$2:A$1016,A$2:A$1016<>""),IF(COUNT(SEARCH(" "&n&" ",SUBSTITUTE(" "&J2&" "&K2&" ","-"," "))),"Name List",""))

Remove all the empty cell values from col A and store them in a variable called "n"
Put a space character before & after each name stored in n. This is how we stop "Jo" from being found in "John".
Join the col J & K names with a space between and also add a space before/after each joined pair. Also replace "-" with a space character since you wanted "Smith" to be found in "Char-Smith" - I think? (If that is not the case we can remove that SUBSTITUTE part and shorten the formula)
Now search for each of the col A values (with space before/after) in the red part. For each of those approx. 1000 searches, if found it will return a number, if not it will return an error. COUNT will return how many numbers - that is, how many successful searches. If that number is anything other than 0 then it has found at least one name so returns "Name List" otherwise returns ""

Hope that makes some sense.

 

[xdriver]

=LET(n,FILTER(A$2:A$1016,A$2:A$1016<>""),IF(COUNT(SEARCH(" "&n&" ",SUBSTITUTE(" "&J2&" "&K2&" ","-"," "))),"Name List",""))

Remove all the empty cell values from col A and store them in a variable called "n"
Put a space character before & after each name stored in n. This is how we stop "Jo" from being found in "John".
Join the col J & K names with a space between and also add a space before/after each joined pair. Also replace "-" with a space character since you wanted "Smith" to be found in "Char-Smith" - I think? (If that is not the case we can remove that SUBSTITUTE part and shorten the formula)
Now search for each of the col A values (with space before/after) in the red part. For each of those approx. 1000 searches, if found it will return a number, if not it will return an error. COUNT will return how many numbers - that is, how many successful searches. If that number is anything other than 0 then it has found at least one name so returns "Name List" otherwise returns ""

Hope that makes some sense.

Makes perfect sense. Thank you for helping with the equation and the explanation.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.