Using vLookup to get User ID from another workbook

[dbe594]

I'm trying to verify account setups for a list of people.

• In one workbook ("workbook1"), we have the list of users by name.
• In another workbook ("workbook2"), we have users listed by name, and...
  their records also includes a User ID column with values like "abcd123" (4 alpha characters and 3 numeric).

I need to review the "workbook1" data to see if the users already have been setup with access (have a User ID), or if they still need to be setup (no User ID).

I'm trying the following vLookup forumla in cell A3...

=VLOOKUP(E3,'https://users/dbe4876/Documents/[workbook2]sheet1'!$B:$O,1)

workbook1

User ID	Business Unit	Employee's First Name	Employee's Middle Name or Middle Initial	Employee's Last Name
#N/A​	Sales	John	A	Doe
abcz999	Marketing	Jill	A	Doedoe

workbook2

User ID	Initials	Assign To
abcd123	JAD	Jill Doedoe
abce234	JAD	John Doe

So, I'm trying to tell it to use the value of column E in "workbook1", to find the same value in the array of "workbook2", and return the value of column 1 for that record/row in "workbook2", back to column 1 in "workbook1".

Am I making sense? Unfortunately, I am either getting "#N/A" or the value of column 1 in "workook2" for the last record in the array (regardless of last name match). And yes, the name is the full name in "workboo2", so I'm hoping this will find the last name even so.

 

[AhoyNC]

Try using XLOOKUP, (VLOOKUP can only return a value to the right. Your ID's are to the left of the look up).
You are also going to have to join the first and last name together.

Book1
	A	B	C	D	E
1	Wooksheet1
2	User ID	Business Unit	Employee's First Name	Employee's Middle Name or Middle Initial	Employee's Last Name
3	abce234	Sales	John	A	Doe
4	abcd123	Marketing	Jill	A	Doedoe
5	No ID		Sally		May
6
7
8	Worksheet2
9	User ID	Initials	Assign To
10	abcd123	JAD	Jill Doedoe
11	abce234	JAD	John Doe
12			Sally May
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=XLOOKUP(C3&" "&E3,$C$10:$C$11,$A$10:$A$11,"No ID")

 

[dbe594]

Try using XLOOKUP, (VLOOKUP can only return a value to the right. Your ID's are to the left of the look up).
You are also going to have to join the first and last name together.

Book1
	A	B	C	D	E
1	Wooksheet1
2	User ID	Business Unit	Employee's First Name	Employee's Middle Name or Middle Initial	Employee's Last Name
3	abce234	Sales	John	A	Doe
4	abcd123	Marketing	Jill	A	Doedoe
5	No ID		Sally		May
6
7
8	Worksheet2
9	User ID	Initials	Assign To
10	abcd123	JAD	Jill Doedoe
11	abce234	JAD	John Doe
12			Sally May
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=XLOOKUP(C3&" "&E3,$C$10:$C$11,$A$10:$A$11,"No ID")

It took me a few tries to apply it to my specific workbooks and ranges but I finally got it!!

Thank you!! :D

 

[AhoyNC]

Glad you got it to work. Thanks for the feedback.