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 · Accepted Answer · 2014-05-28 18:06:19Z

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!

MPękalski · Accepted Answer · 2014-05-28 17:57:38Z

0

You may concatenate the strings and provide that as an argument for MATCH or INDEX function. Still it would not work precisely as you do not have second names/initials like

MATCH(B1 & ", "& A1, Sheet2!C2:C2340, -1)

Although, you need to provide more details to get anything more from the StackOverflow community.

answered May 28, 2014 at 17:57

MPękalski

7,1045 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

Using substrings within an INDEX MATCH function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related