Find Partial Matching data across two sheets

Question

I have two excel sheets that I am working with. Sheet 1 has a 'last name' column D and sheet 2 has a 'name' column A. the 'name' column contains last,first name so I need a partial match option to find the customers last name in each sheet and create a new list using the information found in the 'name' column A from sheet 2 in another sheet. This may be simple but for someone who lacks in excel I would appreciate the help.

Answer 1

You could perform an INDEX MATCH function with wildcard search.

=INDEX(B:B,
MATCH("*"&D2&"*",Sheet2!A:A,0),1)

The match function will search for the partial string in cell D2. (Which would be a last name assuming D1 is the title of the column) - The "*"& part on either side of D2 searches for the partial string. If the column only contained last names, it would just be

=INDEX(B:B,
    MATCH(D2,Sheet2!A:A,0),1)

After it searches for the partial string, it will search through column A in Sheet2 and find the name which matches (Lets hope you do not have duplicate names). Once you have matched two names, in comes the INDEX function. This will obtain the corresponding value from any column you want but only in the same row as that last name.

If you want it all in another sheet, you could do.

=INDEX(Sheet1!B:B,
    MATCH(Sheet1!"*"&D2&"*",Sheet2!A:A,0),1)

Maybe chuck in some error handling as well.

=IFNA(INDEX(Sheet1!B:B,
        MATCH(Sheet1!"*"&D2&"*",Sheet2!A:A,0),1), "No Match or No Data")