Fix SharePoint List Form Conditional Formula Using a Lookup Field
Question details
The user needs to configure a conditional formula in a SharePoint list form using a lookup field to dynamically show or hide a column.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Setting up form-formatting configurations to conditionally display list columns based on a selected lookup field value.
- Observed behavior
- The conditional formula fails to open the target column when comparing the lookup field with text values or IDs, even when using substring functions.
Ensure you have the exact internal field name of your lookup column, as formulas will fail if they reference the display name instead of the internal name.
Use Direct Text Comparison Without Boolean Quotation Marks
Fix formula syntax errors by referencing the lookup value directly and removing quotation marks around TRUE and FALSE boolean results.
SharePoint conditional formatting is highly sensitive to syntax. Using double quotation marks around text values can sometimes cause save errors depending on your regional settings or formatting context. Switching to single quotes and removing quotes entirely from boolean outputs resolves most formula evaluation failures.
Navigate to your SharePoint list, open the form you want to customize, and access the conditional formatting configuration pane for the specific column.
Enter the formula comparing the lookup field directly to the text value. Use single quotes for the text value and omit quotes for the boolean results, for example: =IF(OU='Customer Service',TRUE,FALSE).
Save the formula. If it still fails, check the list settings to verify the internal field name of your lookup column and ensure the lookup is returning text rather than an ID (like OU:ID).
Add a new record or edit an existing one to test whether selecting the target lookup value successfully triggers the column to appear.
Looking for a Lightweight, Reliable Office Suite? Try WPS Office
While SharePoint configuration requires Microsoft-specific troubleshooting, you don't need heavy subscriptions for your everyday document tasks. WPS Office provides a free, lightweight, and highly compatible alternative for word processing, spreadsheets, and presentations.
- 1. Download the installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Run the setup: Open the downloaded installation file and follow the straightforward on-screen instructions.
- 3. Start creating: Launch WPS Office to immediately begin creating, editing, and saving your documents in familiar formats.
Frequently Asked Questions
Why does my SharePoint conditional formula refuse to save?
Formulas often fail to save due to minor syntax errors. Common issues include using double quotation marks instead of single quotation marks around text strings, missing parentheses, or wrapping Boolean results (TRUE/FALSE) in quotation marks.
How do I find the internal name of a SharePoint column?
Go to your List Settings and click on the specific column name. Look at the URL in your browser's address bar; the text immediately following 'Field=' is the exact internal name you must use in your formulas.
Should I use the lookup ID or the text value in conditional formulas?
It is generally recommended to start by referencing the text value directly using standard formula evaluation. If that fails due to how the lookup is structured, you may need to use advanced JSON formatting to reference the ID property (e.g., [$FieldName.lookupId]).
