Excel index and match function with wild card string

Question

I'm trying match a string from spreadsheet 2 with string with wild card in between two cell values from spreasheet 1 and then return value from column C from Spreadsheet 1 into column B into spreadsheet 2 when matched.

Spreadsheet 1

A               B                C 
Jonathan        Smith            1234
Sarah           Jones            1235

Spreadsheet 2

A                             B 
Jonathan Harry Smith          Return value from column C from Spreadsheet 1 - expected to be 1234     
Sarah Jones                   Return value from column C from Spreadsheet 1 - expected to be 1235

Formula I tried is

=INDEX(A:A, MATCH('Spreadsheet 1'A1&"*"&'Spreadsheet 1'B1,'Spreadsheet 1'C:C,0))

The formula doesn't seem to work as it's not returning the correct value from column C from spreadsheet 1, it returns a value for a different person. Where I have a gone wrong? Thank you.

M. Wise M. Wise · Accepted Answer · 2018-10-30T03:18:36

Your formula appears to be backwards as it would return the name on Sheet2 rather than the value in Column C of Sheet1. It is also matching based on the order of Sheet1 rather than the order of Sheet2. Had the sheets been in a different order, the formula would fail.

I'm not entirely sure the excel wildcard works in the way you need it to. I would suggest parsing out the first and last names of Column A in sheet2 and match them with Columns A and B in Sheet1. This will work as long as there are either 1 or 2 spaces in Column A of Sheet2. Try this formula in Cell B2 of Sheet2:

=INDEX(Sheet1!C:C,MATCH(IFERROR(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2))),IF(Sheet1!A:A=LEFT(A2,SEARCH(" ",A2)-1),Sheet1!B:B),0),1)

This formula works without entering it as an array formula, which surprises me. To be safe, I would treat it as an array formula by executing it with CTRL + Shift + Enter.

Excel index and match function with wild card string

1 Answers