I've recently been spending a bit more time answering questions over on SharePoint StackExchange. Yesterday, I responded to a question asking how to limit SharePoint Lookup values based on the user using the Lookup field and what they have access to.
The solution is fairly simple -- use item-level permissions on the list used by Lookup for information.
In the example posted in the StackExchange question, the person asking the question is attempting to connect a Product list with a Customer list via a shared Customer Code Lookup field.
The asker suggests that they may be able to meet their requirement by splitting up all customer codes into multiple lists and then securing those lists. But, this design won't work because an out-of-the-box (OOTB) SharePoint Lookup field can only have one source list for values. By splitting up customer codes into multiple lists, the Product list will need to have as many Customer Code Lookup fields as there are multiple Customer lists.
The asker also considers using List Views in an attempt to filter values. However, List Views are not designed to enforce permissions. This design will not work either. Futhermore, OOTB Lookup fields cannot use List Views as the source for information.
While in my answer I propose using item-level permissions, I also suggest that the asker change their Customer list design. I suggest that there only be one Customer Code record per customer in the Customer list instead of mutlple duplicate Customer Code records per customer used to support associated contact information.
I propose changing the askers original Customer list (List 2) schema from this:
to this revised list schema:
Wait, but why this list schema revision?
Because, while the asker can definitely create multiple duplicate Customer Code list items/records using the original design and those records can be individually secured to only show one of the duplicates to the users there is a very subtle problem. Behind the scenes, what the users don’t notice is that SharePoint "knows" these duplicates are not the same Lookup values and this will result in confusion and problems down the line.
Let’s take a closer look using some mock Customer list data. In the following example, you’ll notice there are two records with at Title of “Cust1”. This Title field is the equivalent to the Customer Code in the original List 2 schema design. As far as the users are concerned both “Cust1” are the same.
But, when this field is connected via a Lookup field and a users selects a value in the Products list, SharePoint will also store the selected Customer list item’s integer identifier under the covers.
If we examine the data stored by SharePoint internally you'll notice that there is an identifier stored for each lookup value selected by the users and these are not the same even though the selected “Cust1” code looks the same to the users in the web interface! In reality, these sample “Cust1” codes are actually stored as two different values “1;#Cust1” and “4;#Cust1”.
So later when users begin to use the “Cust1” code(s) in list headers or list view definitions to filter or sort they will get unexpected results because as you now can see, “Cust1” may not be the “Cust1” the users thinks it is.