How to Migrate SharePoint Lists with Lookup and Person Columns
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.

- 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.
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.
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.
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.
Open Power Automate and select 'Create' > 'Instant cloud flow' (for a manual trigger). Name your flow and click 'Create'.
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.
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.
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.
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.

Export to Excel and Reconfigure Missing Columns Manually
If you only have a few records, you can use Excel to transfer the basic text data and manually re-enter the complex column data in the new list.
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. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open your exported list: Launch WPS Spreadsheet and open the .xlsx file you exported from your SharePoint list.
- 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.

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.




