How to Export a SharePoint 2013 List with Lookup Columns

In this post I’m going to show you a workaround to an issue that causes data loss when exporting lists with lookup columns to another site.


We have 2 lists in a SharePoint 2013 site, one with a lookup column to the other, and we need to export them to another site without loosing any data.

Export List with Lookup - List A

Export List with Lookup - List B


In the List Settings page of our lists we can use the “Save as Template” functionality to export them.

Export List with Lookup - Save as Template

The list templates are successfully created so we can load them to the destination site collection and recreate the lists from them in the new site.

Note: the destination site language must be the same as the source one

Export List with Lookup - List Templates

However looking closer to List B we note that all the data in the lookup column has been lost…

Export List with Lookup - List B with Lost Data

This happens because the lookup column points to List A GUID, not its path or name…

The GUID of List A in the destination site is different from that of the same list in the source site, so the lookup column is now pointing to a list that does not exist in the new environment.

Unfortunately we can’t change the lookup column definition in the destination site and get our data back because the “Get information from: ” section is locked…

Export List with Lookup - Lookup Column Locked

So, how to solve this problem?


We can change the list template manifest file and set the correct list GUID or path for the lookup column.

Let’s see how to do that in detail…


1. Change the list template file extension

Download the template file of our list with the lookup column (in our example the file is ListBTemplate.stp) and change its file extension from “stp” to “cab”.

Export List with Lookup - List B CAB Extension

2. Extract the files contained in the list template cab package

You can use WinRAR or WinZip to extract the files contained in the cab file

Export List with Lookup - List B Extracted Files

3. Change the list template manifest file

Open the manifest.xml file contained in the list template extracted files (in our example it is the only file) and replace the lookup column list GUID (in our example the GUID of list A in site A, f8088b28-1434-4b51-96d3-a36e945e5146) with the new list GUID or relative path (in our example the GUID or relative path of list A in site B, 265bffc8-dcc9-48e3-99e4-d3ad1d5e2260 or Lists\List A).

Export List with Lookup - Manifest

4. Recreate the list template package

Now that we have changed the content of our manifest file we need to recreate the cab package. To do that we ca use the makecab command in the command prompt using the following syntax:

makecab "<source file>" "<destination file>"

In our example the specific command is as follows:

makecab "C:\List Templates\ListBTemplate\manifest.xml" "C:\List Templates\ListBTemplate\"

Export List with Lookup - makecab

Export List with Lookup - makecab result

Once created the cab file we can change its file extension to “stp”, reload it to the destination site collection and recreate our List B.


This is how our new list with the lookup column should look like:

Export List with Lookup - List B without Lost Data

You can see that our lookup data is there again without any loss.




Why SharePoint 2013 Calculated Columns will Save your Life – Filtering Lookup Values

I’m going to show you a no-code, out-of-the-box, simple way to create a filtered lookup column which will work with SharePoint 2013 On Premises and Online.


A client comes up with the following requirement:

We have two lists

  • List A (Title, IsSelectable)
  • List B (Title, Lookup to List A)

We want to filter lookup values so that only selectable items are shown to users to choose from.

List A and B


Use a calculated column in List A and write the formula like this:


Tip: if you receive a syntax error try and replace the “,” (comma) with “;” (semi-colon). Site regional settings can affect the syntax used in SharePoint.

List A and B - Filter Applied


  • Out-Of-The-Box only
  • No InfoPath forms
  • No SharePoint Designer
  • No JavaScript/JQuery/XSLT
  • No Visual Studio code

Time to implement: 3 minutes.


If you should change an item to be no longer selectable then in the list where you have used it its value disappears. That behavior could be ok for most scenarios but be aware of it.

Alternative Solutions

There is a solution on Codeplex which creates a custom site column to handle this. However that solution is not compatible with SharePoint Online because created using server side code.





April Dunnam