Ok, so I have solved my own issue, but I'm absolutely certain that I've gone down a rabbit hole that was entirely unnecessary and that there's a simple solution out there. I also had to change the Manager column in the target list so that it returned the Account name rather than display name

This is my formula 

If(not(isNullOrEmpty(lookup('StoreLocations','Title',parseLookup(StoreLookup),'Manager'))),userProfileLookup(replace(parseLookup(lookup('StoreLocations','Title',parseLookup(StoreLookup),'Manager')),""0-9]*]",""),"PreferredName"),userProfileLookup(replace(parseLookup(lookup('Approvers','Title','Procurement','Name')),""0-9]*]",""),"PreferredName"))

Blue text is the first part of the IF argument checking to see if the lookup I want to do has a value in it (if not it will default to the lookup in green)

Orange text does a does a lookup to a list looking for the Manager of the store based on the parsed "StoreLookup" field and then trims the trailing ] off the manager login that is returned which appears to stop the userprofilelookup from working.

Green text does the same but from a different list.

Please, someone tell me there's an easier way to do this. The problem all stemming from my lookup returning ] around the login name of the user it returns.

Would you be able to just pass the [blah.blah] through a RegEx to remove the brackets and ID?

Just thinking out loud, I haven't sussed this out entirely.

PS: I like your colour coding. Easy to read!

square brackets denote you got returned array of values.

I would say, either you have configured manager field to accept multiple persons, or lookup might match several rows in remote list.

Thanks Marian, this was the bit I was missing. 

Unfortunately I do need the multiple users in the field as there can be more than one manager, so my convoluted formula to strip out the bits and pieces I need is required. 

That's what's I've done in my solution, thanks.

hm, I affraid your formula is not going to work if you simply strip off brackets,

if you get more then 1 values in the array, they will be separated by a comma. so if you strip off brackets it will break syntax of your formula.

at second, userProfileLookup() accepts just single user account.

Good point, for the purposes of this particular task, I only need a single manager so I'll strip off anything after the comma. I guess if I needed both managers then I could have two controls, one looking for the first manager (before the comma), the other looking for the second (after the comma) and have them hidden on the form. I know there will not be more than 2 managers at this time. Otherwise we're into Javascript to put them into an array and make the call, but I'm trying to avoid code options here as there isn't anyone technical to take over ownership of the form when I move on.

I would say to maintain the formula will not be much easier

I have same problem. I have two lists where one list has the dept column. and other list with two column 1. dept (lookup column from first list ) and 2. email id.

Now in the third list i have the Nintex form with dept field to get the dept and std apporvers which will look from the 2nd list.

I used the calculated value with lookup formula. and it is returning [ blah , blah, blah] .

Now how cal i split or remove the [] and , (comma) and put ;(semicolon) instead of , (comma).

Thanks for your help

Yogesh

Nintex workflow designer

replace(parseLookup(lookup(ListTitle","Column","ColumnValue","Output")),"[|""|]","")