Lookup from a list via calculated value

  • 12 February 2018
  • 4 replies

Badge +11


I am trying to implement the Lookup function by using the calculated value in a form.

Somehow, it does not seem to fetch the data from a People of Group field. ?!

There is a custom lists named:  Approvers which holds following column names:
note: the view is called: AllItems.aspx

  • Title
  • EmployeeID
  • EmployeeName
  • Approver

On the form, I inserted the Author field which shows "CurrentUser".

The calculated value holds the following:  lookup("Approvers", "ID", Author, "EmployeeID")

However, it does not pull the information apart from #Value!

The Author is a People or Group field whereby I use the currentUser function to input the current user's name in.

Hint: I also used the "http://domain.com/Lists|Approvers"

4 replies

Badge +9

lookup(list title, column to filter on, value to filter on, output column)

In the formula you want to filter by the list item ID.  But the value to filter on is a person field. This can't work.

lookup("Approvers", "ID", Author, "EmployeeID")

Badge +11

Grüss Dich Manfred,

Right, the Approver list has the above setup,

  • Title
  • EmployeeID  (Employee ID)
  • EmployeeName (Employee Name)

Now to my lookup field: the Author is the Person or Group name.
I also tried the lookup("Approvers", "EmployeeName", Author, "EmployeeID") but not working sad.png

Badge +5

Hi Bim, Try to use this function to pass the Author information. It will be match with People Group picker name from your list. 

userProfileLookup(Current User,"PreferredName")

Current User will return the value "i:0#.w|domainusername".

Badge +11

Hey Pyae.

your suggestion works only when using the "calculated value" within a form happy.png
However, the userProfileLookup does not exist in a People Picker field :/

So, now I will have to get rid of the People or Group fields and use calculated value instead.