Reversing my lookup for a reversed name

[Areallusernamestaken]

Hi,

Thanks in advance for your help through this amazing forum! I wish I could be more help to others here!

I am trying to fill in Employee IDs in column A by pulling them from a separate list based on names.

I have the following formula, which pulls ID numbers from a separate list based on names: =TRIM(IFERROR(INDEX(separate list column A,MATCH(RIGHT(B2,LEN(B2)-FIND(" ",B2))&", "&LEFT(B2,FIND(" ",B2)-1), separate list column B,0),1),"Fill in"))

CURRENTLY,

Active sheet:
A B

Emp. ID	Name
	Derrick Domingo
	Beth Brown
​	Amber Amigos

Separate List:

CURRENTLY, the list it's pulling from (Separate List) has the names listed like this: Last, First
while the active sheet has the names listed like this: First Last

I now have a NEW Active Sheet where the names are listed Last, First. The Separate List remains the same.

How can I adjust my formula for this new format? I would still like it to look for last name, look for first name, and if it contains both, pull up the matching ID from Separate List. I also don't want it to be thrown off by the presence of a middle initial.

Hope that was clear.

Thank you!

 

[Rick Rothstein]

Assuming your separate list is located on a worksheet named "Separate List", give this formula a try...

=INDEX('Separate List'!A:A,MATCH(MID(B2&", "&B2,FIND(" ",B2)+1,LEN(B2)+1),'Separate List'!B:B,0))

 

[Areallusernamestaken]

Thank you. I now have:

=TRIM(INDEX('Separate List'!$A:$A,MATCH(MID(B2&", "&B2,FIND(" ",B2)+1,LEN(B2)+1),'Separate List'!$B:$B,0),1))

But it is giving me a #N/A error.

(I did also try it the way you had it, without the 1 at the end.)

Any ideas?

 

[Areallusernamestaken]

Would it be because not all have a middle initial?

 

[Rick Rothstein]

What middle initial? Your examples did not show any names with middle initials. That would definitely affect things if you have a mix like that. Please show what you posted initially but with truly representative data so we can see what you actually have to work with. Make sure to include periods if the middle initial has them. Do you have some with periods and others without?

 

[Areallusernamestaken]

On the Separate List, some of the names have middle initials. None have a period.

So a name might appear as 'Domingo, Derrick' on the Active Sheet and as 'Domingo, Derrick W' on the Separate List.

I am trying to write a formula that does the following: IF a cell on the Separate List contains both the first name and last name contained in the cell on the Active Sheet (regardless of possible middle initial), THEN I want it to pull the corresponding ID number from the Separate List.

 

[Rick Rothstein]

When there is a middle initial, will it only be in the Separate List?

If not, is it possible for the middle initial to be on the active sheet but not in the Separate List?

 

[Areallusernamestaken]

When there is a middle initial, will it only be in the Separate List?

If not, is it possible for the middle initial to be on the active sheet but not in the Separate List?

I don't believe a middle initial would be on the Active Sheet but not on the Separate List. However, I don't know that for sure, so if the formula could account for that possibility, that would be ideal.

 

[Areallusernamestaken]

This might help make the question clearer:

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

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

www.mrexcel.com

I'm trying to do the same thing, EXCEPT this time, in Spreadsheet1, the names are listed Last, First.