Fix SharePoint List Conditional Formatting for Lookup Columns
Question details
The user is unable to get a conditional formatting formula to work on a list column when referencing a lookup value from another SharePoint list.
- Product
- SharePoint Online
- Device & OS
- not provided
- Scenario
- Setting up conditional column formatting or conditional visibility in a SharePoint Online list form based on values in a referenced lookup column.
- Observed behavior
- The conditional formula fails to apply and does not execute the desired show/hide or formatting behavior when referencing the lookup column.
Ensure you have the appropriate site permissions to edit list settings and customize forms in SharePoint Online. Additionally, verify that your lookup column is correctly connected to the source list and contains active data.
Update Formula Syntax for Lookup Column References
Modify the conditional formatting JSON to target the specific property of the lookup object instead of treating it as a standard text column.
In SharePoint Online, a lookup column returns an object rather than a simple text string. If your conditional formula treats the lookup column like a standard text field, it will fail to evaluate correctly. You must specify that you want to read the text value of the lookup object.
Navigate to the List Settings gear icon, select 'List settings', and click on your lookup column (e.g., 'Types'). Look at your browser's address bar; the text immediately following 'Field=' is the internal name you must use.
Update your conditional formula reference. Instead of writing `[$Types]`, use the `.lookupValue` property by writing `[$Types.lookupValue] == 'YourTargetValue'` to accurately read the text.
To apply conditional visibility in the list form, click 'New' to open the form, click the 'Edit form' icon at the top right, select 'Edit columns', choose 'Edit conditional formula' next to the 'Exceed' column, paste your updated formula, and click Save.
Boost Your Productivity with WPS Office
While SharePoint handles complex, enterprise-level data lists, everyday tasks require a robust and easy-to-use office suite. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office for creating, managing, and collaborating on your documents, spreadsheets, and presentations.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install the Suite: Run the downloaded installer and follow the quick on-screen setup instructions.
- 3. Open Your Files: Launch WPS Office to immediately open, edit, and save your existing Office documents with full formatting integrity.
Frequently Asked Questions
Why does my conditional formatting formula work for text columns but not lookup columns?
Text columns output simple strings, whereas lookup columns output complex data objects containing an ID and a value. You must explicitly tell SharePoint which part of the lookup object to evaluate by adding '.lookupValue' or '.lookupId' to the internal column reference.
Can I hide an entire SharePoint list column based on a lookup value?
Yes. You can use declarative JSON formulas in the list form settings to show or hide columns. By using an expression such as `=if([$Category.lookupValue] == 'Urgent', 'true', 'false')`, the column will only display when the condition is met.
Where do I enter the conditional formatting formula in a SharePoint list?
For column formatting, click the column header in the list view, select 'Column settings', then 'Format this column', and click 'Advanced mode'. For form visibility, open the item creation form, click the 'Edit form' icon, choose 'Edit columns', and select 'Edit conditional formula' next to the target column.
