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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,929
Office Version
  1. 365
Platform
  1. Windows
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")
 

dbe594

New Member
Joined
Aug 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,929
Office Version
  1. 365
Platform
  1. Windows
Glad you got it to work. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,846
Messages
5,833,937
Members
430,247
Latest member
w9u5280o

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
Top