Calculated Lookup field returning #Value!

[#1]

I am using a list with 4 lookup calculated fields, 3 of them return the correct data while 1 shows "#Value1". The difference with that one is it is looking up a lookup column selected.

Here is a picture:

Once I select "Jon Ngo" in Original Internal Notification, the calculated fields populate Employee Name, Current Office Number, and Current Title (all three being single-line of text). The Current Department is not showing the name of the department, but instead  #Value! (this is pulling from a lookup column from another list called 'Department')

Am I doing something wrong?

[eharris04]

Jon,

I don't see anything majorly wrong from what you provided.  I would suggest that you disable the other fields and start with one at a time, then add them back.  I would pull the title in to see if something their is just being buggy.  If so then you at least know where to start looking. 

Hope that helps.

[#3]

Hi Jon Ngo​,

How is the data stored in the list? Are you querying the data based on "Original Internal Notification" so "Jon Ngo" in you example?

Lookup lists / columns work with the List Item ID of the item that you want to retrieve from "Department". So when you use "Jon Ngo", it does not retrieve a value. You would need to get the ID of the Department in the list.