Excel formula: search in column by another value (LIKE)-CodePudding

I'm having a problem in Excel, where I'm trying to search an entire column for a name (not necessarily 100% identical) that is in another column.

The following table for a better explanation:

---------------------------------------
NAME          CITY       FATHER NAME     
---------------------------------------
Saad Test     New York   William Jack
Jack Jacking  Paris      Noah Saad   
Adam King     Rabat      William Sara
Sara Best     Madrid     Benjamin Adam

Briefly: I want to get the father's name by using only the name column. Is there a way to do that? (The order of the fathers’ names is incorrect, so I want to search using the name, if it is present in the father’s name, then it is the result I want)

Example: Saad test his father's name is Noah Saad (Match by Saad)

PS: I tried using LOOKUP, VLOOKUP and MATCH, but unfortunately the result is always N/A

PS 2: The data I have in Arabic is the reason why the above functions are not working?

CodePudding user response：

I would use index() with match(), assuming your data starts in cell A1, like so:

=index(C1:C4,match(F1,A1:A4,0))

F1 contains the name that you look for.

Note, the classic problem is that there are extra spaces (leading or trailing) that make the names not identical.

You can trap errors as so:

=iferror(index(C1:C4,match(F1,A1:A4,0)),"Check")

CodePudding user response：

You could do:

=INDEX( $C$2:$C$5, MATCH( "*" & MID( A2, 1, FIND( " ", A2 )-1), $C$2:$C$5,0) )

SolarMike - I think you have said the same, so I'm sorry if I am stepping on your answer.