I have created a field that is of type "user" and set to "current user". Now I also have a User table that specifies users (first name, last name) along with the department they belong to amongst other attributes. How would I look up the user: department field value after matching the current user to the user name fields in the User table?
Just set the Key field of the User Table to be the Userid field. Then make a relationship down to your details table based on that field. It's a hugely powerful technique. Aside from just looking up user attributes, it can be used to control security access.
For example if you only want to allow a user to be able to see their own "department". Rather than have the terrible clutter and nightmare maintenance of 10 or 20 Roles per each department, you just maintain a table of users and limit them in a single Role to "their" department on their user table which is looked up down to any details tables.
ie a Role Permission where [department] = [My lookup department]
Make a formula checkbox field called perhaps [user is allowed to see] or [This is My department]
The formula would be
[Department] = [User lookup Department]
Then set the Custom Role permission where [Allowed to see] is checked.