Using vLookup to get User ID from another workbook

dbe594

New Member
Joined
Aug 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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...
Excel Formula:
=VLOOKUP(E3,'https://users/dbe4876/Documents/[workbook2]sheet1'!$B:$O,1)

workbook1
User IDBusiness UnitEmployee's First NameEmployee's Middle Name or Middle InitialEmployee's Last Name
#N/A​
SalesJohnADoe
abcz999MarketingJillADoedoe

workbook2
User IDInitialsAssign To
abcd123JADJill Doedoe
abce234JADJohn 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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
ABCDE
1Wooksheet1
2User IDBusiness UnitEmployee's First NameEmployee's Middle Name or Middle InitialEmployee's Last Name
3abce234SalesJohnADoe
4abcd123MarketingJillADoedoe
5No IDSallyMay
6
7
8Worksheet2
9User IDInitialsAssign To
10abcd123JADJill Doedoe
11abce234JADJohn Doe
12Sally May
Sheet1
Cell Formulas
RangeFormula
A3:A5A3=XLOOKUP(C3&" "&E3,$C$10:$C$11,$A$10:$A$11,"No ID")
 
Upvote 0
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
ABCDE
1Wooksheet1
2User IDBusiness UnitEmployee's First NameEmployee's Middle Name or Middle InitialEmployee's Last Name
3abce234SalesJohnADoe
4abcd123MarketingJillADoedoe
5No IDSallyMay
6
7
8Worksheet2
9User IDInitialsAssign To
10abcd123JADJill Doedoe
11abce234JADJohn Doe
12Sally May
Sheet1
Cell Formulas
RangeFormula
A3:A5A3=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
 
Upvote 0
Glad you got it to work. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top