IF both first and last name are in the cell, spit back ID

[Areallusernamestaken]

Hi,

I am trying to import ID numbers from spreadsheet2 into spreadsheet1. Spreadsheet2 contains both names and ID numbers, while spreasheet1 contains just names.

However, the names don't always appear identically on both lists. For example, a name might appear once with a middle initial and once without a middle initial.

Spreadsheet1:

Spreadsheet2:

To prevent it from giving an error when it doesn't match exactly, I tried to circumvent it by splitting the name column on spreadsheet2 into Last, First, and MIddle initial columns and then telling it that it needs to contain both the first and last name in order to return the ID. I wrote the following formula:

=IF(AND(ISNUMBER(SEARCH('Spreadsheet2'!L2,B2)),ISNUMBER(SEARCH('Spreadsheet2'!M2,B2))),INDEX('Spreadsheet2'!$A:$A,MATCH(B2,'Spreadsheet2'!$B:$B,0)),"Fill in")

What am I doing wrong?

Thank you!

 

[rilzniak]

Hi,

I am trying to import ID numbers from spreadsheet2 into spreadsheet1. Spreadsheet2 contains both names and ID numbers, while spreasheet1 contains just names.

However, the names don't always appear identically on both lists. For example, a name might appear once with a middle initial and once without a middle initial.

Spreadsheet1:
View attachment 37617
Spreadsheet2:
View attachment 37616

To prevent it from giving an error when it doesn't match exactly, I tried to circumvent it by splitting the name column on spreadsheet2 into Last, First, and MIddle initial columns and then telling it that it needs to contain both the first and last name in order to return the ID. I wrote the following formula:

=IF(AND(ISNUMBER(SEARCH('Spreadsheet2'!L2,B2)),ISNUMBER(SEARCH('Spreadsheet2'!M2,B2))),INDEX('Spreadsheet2'!$A:$A,MATCH(B2,'Spreadsheet2'!$B:$B,0)),"Fill in")

What am I doing wrong?

Thank you!

The problem is:

MATCH(B2,'Spreadsheet2'!$B:$B,0)

You're trying to match Ben Robins in a list that doesn't contain that entry. B2 (Ben Robins) cannot be located in B:B (Robins, Ben).

Try:

=INDEX(Spreadsheet2!A:A,MATCH(RIGHT(Spreadsheet1!B2,LEN(B2)-FIND(" ",Spreadsheet1!B2))&", "&LEFT(Spreadsheet1!B2,FIND(" ",Spreadsheet1!B2)-1),Spreadsheet2!B:B,0),1)

That is assuming the name is always listed as "Last, First" without a middle initial.

 

[Areallusernamestaken]

The problem is:

MATCH(B2,'Spreadsheet2'!$B:$B,0)

You're trying to match Ben Robins in a list that doesn't contain that entry. B2 (Ben Robins) cannot be located in B:B (Robins, Ben).

Try:

=INDEX(Spreadsheet2!A:A,MATCH(RIGHT(Spreadsheet1!B2,LEN(B2)-FIND(" ",Spreadsheet1!B2))&", "&LEFT(Spreadsheet1!B2,FIND(" ",Spreadsheet1!B2)-1),Spreadsheet2!B:B,0),1)

That is assuming the name is always listed as "Last, First" without a middle initial.

Hmm... I get what you're saying.

How would I do it, then?

I am trying to tell it that if a cell contains both Ben and Robins, then it should return the ID that matches with that cell.

 

[rilzniak]

Hmm... I get what you're saying.

How would I do it, then?

Spreadsheet1, cell A2:

=IFERROR(INDEX(Spreadsheet2!A:A,MATCH(RIGHT(Spreadsheet1!B2,LEN(B2)-FIND(" ",Spreadsheet1!B2))&", "&LEFT(Spreadsheet1!B2,FIND(" ",Spreadsheet1!B2)-1),Spreadsheet2!B:B,0),1),"Fill in")

You don't need that secondary lookup set in columns L:N on Spreadsheet2.

 

[Areallusernamestaken]

Thank you! That worked!