I’m going to show you a no-code, out-of-the-box, simple way to create a filtered lookup column which will work with SharePoint 2013 On Premises and Online.

Scenario

A client comes up with the following requirement:

We have two lists

• List A (Title, IsSelectable)
• List B (Title, Lookup to List A)

We want to filter lookup values so that only selectable items are shown to users to choose from.

Solution

Use a calculated column in List A and write the formula like this:

=IF(IsSelectable,Title,””)

Tip: if you receive a syntax error try and replace the “,” (comma) with “;” (semi-colon). Site regional settings can affect the syntax used in SharePoint.

Pros

• Out-Of-The-Box only
• No InfoPath forms
• No SharePoint Designer
• No JavaScript/JQuery/XSLT
• No Visual Studio code

Time to implement: 3 minutes.

Cons

If you should change an item to be no longer selectable then in the list where you have used it its value disappears. That behavior could be ok for most scenarios but be aware of it.

Alternative Solutions

There is a solution on Codeplex which creates a custom site column to handle this. However that solution is not compatible with SharePoint Online because created using server side code.

References

MSDN

StackExchange

Balestra

April Dunnam