SharePoint List Lookup: Get User_ID from Username

[dietergletz]

I currently have 2 simple lists:

List A: 

UserID - Username - Color

12 - John - Red

15 - Max - Blue

19 - Mark - Green

List B:

Username - UserID (empty)

John - 

Max - 

Mark- 

All I want that when a new item is added to List B, it will automatically populate UserID depending on the Username. The information can be retrieved from List A. 

I tried to make UserID in List B a lookup field. However, when adding a new item to List B, it does not automatically populate the UserID, instead, it shows a dropdown of all UserIDs from List A. Is there a way for this to be automatic?

[#2]

@dietergletz this is List A which has the User ID (renamed from Title), Username and Color columns.

Then in List B you won't need the Title column. Create a new column which I've called Username, type is Lookup from the Color list then open the more options and select the other columns to be added to list B. It will still show Title instead of UserID so select that and Color.

The additional columns are added to List B. When you add a new item select the username; I'm going to select Chris and save it.

The UserID and Color columns are automatically populated:

Hope that helps.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)