I need to perform an xlookup on a set of sub strings until I get match. So I have a list of names that will appear in various formats in other columns, so I want to search for all parts of that name to get a match.
=XLOOKUP("*"& L56 &"*",Sdx!E1:E250,Sdx!I1:I250,"",2)
So if I have the name John Smith I want to search for John and then if no match search for Smith. John Smith is the string in cell L56 for the above. My list of data may refer to that person as Mr Smith, J Smith, Mr J Smith, Mr John Smith etc
I'll only be searching in a list of 2 people so there won't be issues with multiple instances of similar names.