So this is a follow up from my question yesterday as I believe I have found a work around or at least a potential one.

To cut a long story short I have a list called "Team Information" this contains 2 columns:
1. Name
2. Department

I am now creating a new list which will contain 2 look ups which will need to show options based on department for example:

Lookup 1 - Only show users within Finance
Lookup 2 - Only show users within HR

Yesterday I was looking for ways to achieve this but think I have come up with a solution that just needs a little tweaking. I have created a calculated column in the Team Information list with the formula:


I repeated this for HR and have used these columns as the lookups and now in the dropdowns the only information displayed is names of people within those departments however those not within the specified department are still showing as blanks in the dropdowns. Is there any amendments I can make to the formula to stop these blank values from appearing? I have tried a few but I'm terrible with formulas and can't seem to create one to achieve this.

Thanks in advance!

2 Answers 2


This formula seems to be working correctly now must of just been a page loading error. Will leave this here for anyone else who may find use from this workaround


A caveat incase you need to keep historical data - your historical data can change when you use a variable lookup. (I am running into this issue myself.)

  1. If you edit an existing record associated with a Name that is filtered out of your lookup list, the Name data blanks out on save.

  2. If you change the Lookup list Name from FRED to MARY, the next time you save a record with Name=FRED, it will change to Name=MARY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.