I'm trying to use an INDEX MATCH function to automate filling one spreadsheet using data from another. However, the sheet I am filling has people's names listed in two separate cells (e.g. "John" in A1, "Doe" in B1), while the sheet I am filling from has them listed as "DOE, JOHN Q." in one cell. A regular INDEX MATCH function would never give a result since no cell in the domain contains just the string "Doe." Is there any way to perform the function for cells that have the lookup value within the cell as a substring?
For reference, this is my attempt at this problem, which of course returns an error:
=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(B3,ISNUMBER(SEARCH(B3,Sheet2!C2:Sheet2!C2340)),0))