SharePoint “Look Up” Column Sync Errors in Excel

Lookup column synchronization errors between SharePoint Online and Microsoft Excel occur when Excel workbooks exported from or linked to a SharePoint list cannot resolve data relationships across secondary tables. This typically results in columns displaying #VALUE!, returning blank cells, showing raw [Record] objects in Power Query, or blocking two-way synchronization in Excel tables.

Fast Fix

If using Power Query, click the column header expansion icon on the lookup column and expand the required text values from the underlying record. If editing data via an Excel export, verify that the view has not exceeded SharePoint’s 12-lookup column limit and confirm you have read access to the source list that provides the lookup data.

---

Diagnostic Snapshot

Failure Symptom	Mechanism	Immediate Action
[Record] displayed in Excel	Power Query imports complex JSON lookup objects	Use “Expand Record” in the Power Query Editor
#VALUE! or blank columns	Insufficient permissions to the source lookup list	Grant user Read access on the referenced list
“Lookup limit exceeded”	View includes more than 12 lookup-type columns	Remove unnecessary lookup, person, or metadata fields
Sync blocked in Quick Edit	Invalid data entered violating source list choices	Pick values strictly from the dropdown selector

---

Why Lookup Columns Break in Excel

A SharePoint Lookup column functions as a foreign key relationship. It does not store static text; it stores an ID pointer referencing an item on another list located within the same site collection.

[Target SharePoint List]               [Source Lookup List]
┌─────────────┬──────────────┐         ┌────────────┬─────────────┐
│ ProjectName │ ClientID     │ ──────> │ ClientID   │ ClientName  │
│ Project Alpha│ ID: 42       │         │ 42         │ Contoso Ltd │
└─────────────┴──────────────┘         └────────────┴─────────────┘

When Excel connects to this data, three architectural issues frequently cause sync failures:

1. The Join Threshold (12-Lookup Limit): To preserve performance, SharePoint limits views to 12 lookup columns per query. Note that “People or Group”, “Managed Metadata”, and “Workflow Status” columns count against this 12-column limit because they rely on internal database joins. Exceeding this limit causes data exports to drop column values entirely.
2. Object Flattening in Power Query: Modern Excel imports data via Power Query (Web.Contents or SharePoint.Tables). Power Query ingests lookup data as a structured nested record rather than a plain string. If you do not explicitly instruct Power Query to expand that record, the cells will read [Record] instead of showing the text.
3. Mismatched Source List Permissions: If a user has “Edit” permissions on the primary SharePoint list but lacks “Read” permissions on the secondary list providing the lookup values, Excel will return blank or #VALUE! cells for that column.

If permission errors are preventing users from interacting with files and metadata across the team site, check “Access Denied” to Teams Files despite being Team Member.

---

Progressive Action Steps

1. Expand Nested Lookup Records in Power Query

If you imported SharePoint data into Excel via Data > Get Data > From Online Services > From SharePoint Online List, lookup columns will appear as nested objects.

1. Open Excel and press Data > Queries & Connections.
2. Double-click your SharePoint query to launch the Power Query Editor.
3. Scroll horizontally to find the lookup column. Each cell will display the word [Record].
4. In the column header, click the Expand icon (two arrows pointing away from each other).
5. Uncheck (Select All Columns) and check the specific field you want to display (e.g., Title or Value).
6. Uncheck Use original column name as prefix to keep your headers clean.
7. Click OK, then click Close & Load in the top-left corner. Excel will replace the [Record] markers with the actual text values.

Power Query Header:
[ CustomerLookup ]  <── Click [⤢] Expand Icon
                         ├── [ ] Select All
                         ├── [x] Title       <── Checks text display
                         └── [ ] Id

For broader connection issues between Excel and cloud document stores, see Troubleshooting SharePoint Folder Connection Failures in Power Query.

2. Reduce Lookup Fields Below the 12-Column Ceiling

If an export to Excel fails with an error indicating that the query is too complex, check the number of lookup-type columns in the current view.

1. In SharePoint, click Settings gear > Library settings > More library settings.
2. Look at your columns. Tally every column falling into these categories:
   • Lookup columns
   • Person or Group columns
   • Managed Metadata columns
3. If the total exceeds 12, scroll to the Views section at the bottom of the settings page and click the view you are exporting.
4. Uncheck non-essential lookup fields to bring the count for that specific view down to 12 or fewer.
5. Save the view and re-export the list to Excel.

3. Verify Source List Access Permissions

If some users see valid lookup text in Excel while others see blank cells for the same rows, the issue is access rights to the source list.

1. Identify the name of the secondary list providing the lookup values (visible in the column’s definition under Library settings).
2. Navigate to that secondary list in SharePoint.
3. Click Settings gear > List settings > Permissions for this list.
4. Confirm whether the list has broken inheritance.
5. Ensure the security group containing the affected users has at least Read permissions on this secondary list. Without Read rights, SharePoint filters out lookup values before sending data down to Excel.

4. Audit Data Entry in Synchronized Tables

If you use two-way sync tools to write changes back from Excel to SharePoint and receive column validation or data-type mismatch errors:

• Lookup columns require exact matches against existing entries in the parent list.
• Typing a new, non-existent value directly into an Excel table cell will be rejected by SharePoint during synchronization.
• New lookup choices must be created in the primary source list first before they can be selected in dependent tables.

If column restrictions continue preventing file saves and data entry, review the troubleshooting workflow in Resolving “Column Validation” Errors during SharePoint Sync and Resolving SharePoint “Missing Metadata” preventing File Upload.