Using substrings within an INDEX MATCH function

Question

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))

maciej maciej · Accepted Answer · 2014-05-28T17:54:28

I think you're on the right track. I think your initial formula should work but you need to make two changes:

Match criteria should be "TRUE" instead of "B3", as the result iof the ISNUMBER() function is a TRUE or FALSE
When entering the formula, press CTRL+SHIFT+ENTER instead of just ENTER, to make this an array function

Formula:

=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(TRUE,ISNUMBER(SEARCH(B3,Sheet2!C2:Sheet2!C2340)),0))

If you wanted to search for both the first and last name, you could multiply two ISNUMBER() arrays together, and search for 1 instead of TRUE. This would get it to match both A3 and B3:

=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(1,ISNUMBER(SEARCH(A3,Sheet2!C2:Sheet2!C2340))*ISNUMBER(SEARCH(B3,Sheet2!C2:Sheet2!C2340)),0))

Alternately, you could attempt to match both first and last names together with:

=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(TRUE,ISNUMBER(SEARCH(B3&", "&A3,Sheet2!C2:Sheet2!C2340)),0))

Hope this helps. If I missed something, let me know!

Using substrings within an INDEX MATCH function

2 Answers