SharePoint Document Issues

How to Migrate SharePoint Lists with Lookup and Person Columns

John WilsonJohn Wilson Nov 3, 2026 870 views

Question details

The user needs a method to migrate a SharePoint list to another site while preserving Lookup and Person or Group column values, which are lost when using the default Excel export/import method.

How to Migrate SharePoint Lists with Lookup and Person Columns
Product
Microsoft SharePoint
Device & OS
not provided
Scenario
Migrating a SharePoint list across different sites using the 'Create from Excel' feature, which results in missing complex column data.
Observed behavior
Lookup column values fail to transfer to the new site, and Person or Group column types are unavailable to select when creating the new list from an imported Excel workbook.
Before you start

Ensure you have Site Owner or Full Control permissions on both the source and destination SharePoint sites, and verify that you have access to Microsoft Power Automate.

Solution 1Recommended

Use Power Automate to Migrate Lists and Preserve Column Values

The most reliable method to transfer Lookup and Person/Group columns across SharePoint sites is by building a Power Automate flow to copy the data directly.

Lookup columns in SharePoint strictly reference lists within the same site. When moving a list to a new site, standard Excel exports break these relationships. Furthermore, SharePoint's built-in 'Create from Excel' tool does not support mapping data to Person or Group columns. Using Power Automate bypasses these limitations by copying the raw claims and lookup IDs.

1
Recreate structural lists on the destination site

Manually create the destination list on the new site. Before doing this, ensure you also recreate any lists that act as sources for your Lookup columns so the references have a valid target.

2
Create a new Power Automate flow

Open Power Automate and select 'Create' > 'Instant cloud flow' (for a manual trigger). Name your flow and click 'Create'.

3
Retrieve items from the source list

Add a new step and search for the SharePoint 'Get items' action. Select your original Site Address and the source List Name to pull the existing records.

4
Create items in the destination list

Add another step and search for the SharePoint 'Create item' action. Select your new Site Address and the destination List Name. Power Automate will automatically wrap this in an 'Apply to each' loop.

5
Map the complex columns

Map your standard text fields dynamically. For Lookup columns, map the 'Id' from the source lookup. For Person or Group columns, map the 'Claims' property from the source person column into the destination field.

6
Test and run the migration

Save your flow and click 'Test' in the upper right corner. Run the flow manually to populate your new SharePoint list with all original Lookup and Person data intact.

Use Power Automate to Migrate Lists and Preserve Column Values
Migrate Lookup Sources First: You must completely migrate and populate the referenced lookup source lists on the new site before running the flow for the main list, or the Lookup IDs will fail to match.
Free Microsoft Office alternative

Handle Your Exported SharePoint Data with WPS Office

While complex SharePoint list migrations require Power Automate, managing, editing, and cleaning up your exported Excel lists is seamless with WPS Office. It provides a lightweight, highly compatible, and free alternative to Microsoft Office for all your spreadsheet and data preparation needs.

  1. 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
  2. 2. Open your exported list: Launch WPS Spreadsheet and open the .xlsx file you exported from your SharePoint list.
  3. 3. Edit and save: Review your list data, make any necessary bulk text edits, and save the file to prepare it for your new site.
Fully compatible with Microsoft Excel (.xlsx) formats exported from SharePoint.Free and lightweight suite for editing spreadsheets, documents, and presentations.Familiar user interface ensuring a zero-learning-curve transition from Microsoft Office.Built-in data formatting tools to easily clean your lists before importing them back into SharePoint.
microsoft office alternative - wps office

Frequently Asked Questions

Why are my Lookup column values missing after migrating a SharePoint list?

Lookup columns are tied directly to specific source lists within the same SharePoint site. When you move a list to a new site, the lookup reference breaks because it cannot query lists located on the original site. You must recreate both the source list and the lookup connection on the new site.

Can I map a Person or Group column when creating a list from an Excel file?

No, SharePoint's 'Create from Excel' feature does not currently support mapping data to the Person or Group column type. The column will not appear as an option during the import wizard. You must create the column manually after the import or use Power Automate to populate those specific fields.

Is there a built-in SharePoint feature to perfectly copy an entire list with its relationships?

Out-of-the-box SharePoint does not perfectly preserve complex cross-list relationships (like Lookups) during a basic site-to-site copy. To maintain these relationships automatically, administrators typically rely on third-party SharePoint migration tools or custom Power Automate scripts.