SharePoint 2013: How to create a cross-site lookup column using custom site columns

Problem

I needed to create a list-based, work tracking system for my IT team.  This list would need to link to the IT helpdesk ticketing list used by the general user base.  The ticketing list was in a site visible to the general user, while the work tracking list would be in a subsite visible only to the IT team.  Column lookups only work for lists within the same site.  The challenge then was to expose a list as a lookup to a subsite.

This challenge can be resolved by using custom site columns.  Custom site columns created at the site collection level are available to all sites within the site collection. Custom site columns created at the site level are available within that site and within its subsites.

In this walkthrough, I'll create a custom column at the site level, since this column won't be needed outside of the parent site.  This list will then be exposed as a lookup to another list contained within a subsite of the original list.

Procedure

1. Logged in as a site collection administrator, navigate to the parent site containing the ticket list.
2. Go: Settings > Site Settings > Web Designer Galleries > Site columns.
3. Click Create.
4. Enter a name for the new site column.  For this walkthrough, I'll call it ServiceDeskTickets.
5. Select Lookup (information already on this site) as the type.
6. Select a group to categorize this site column or enter a new group name.  For this walkthrough, the group will be Custom Columns.
7. Configure the next two settings as desired.  Next comes the critical part.
8. From the Get information from dropdown, select the list you want to expose, in this case the service desk ticket list. 

   If you don't see your desired list here, this is probably because you did not go to the right site before clicking Site columns - or you may be at the root site.  Just cancel out of everything, and then start over. 

   After you make your selection, the page refreshes, updating the various options and data listed in the Additional Column Settings group.
9. Then from the In this column: dropdown, select the primary field from this list that you want to expose. For my needs, I chose the ticket list ID, since this would lead to a nicely formatted dropdown to select from in the subsite list.
10. Select any additional list columns that you want to also be exposed to the subsite. 

    You may not see all of the source columns listed here. This is because certain column types cannot be used to create lookup column types. See this reference for additional details on what column types can and can't be exposed in this way.  There are workarounds for still exposing some of these missing columns if you encounter this.

    .
11. Click OK. 
12. Navigate to the subsite, and then to the target list in which you want to expose the cross-site lookup.
13. On the list's LIST ribbon, click List Settings.
14. Scroll down and then click Add from existing site columns.
15. From the Select site columns from dropdown, select Custom Columns.
16. In the Available site columns multi-select box, select ServiceDeskTickets, and then click OK.  The List Information page is displayed again, and you can now see all of the lookup columns that were added to the target list.

    To change the name of any lookup column to something more user-friendly, just click on it.  To change what additional lookup columns will be added, just click on the primary lookup column: this will take you to a page where you can customize what additional lookup columns will be added along with the primary lookup column.

17. You're done.

References

• Creating a cross site lookup field without code
• Using Site Columns in Sharepoint 2013
• Plan content types and workflows in SharePoint 2013
• Projected Lookup Field - Lookup Column Additional Fields Missing
• Content Type or Lists Lookup Column can't show additional fields which are Choice or lookup columns
• Content Type enforcing foreign key relationship
• Create list relationships by using unique and lookup columns

Notes

• Many thanks to Bram Nuyts for presenting the original critical steps necessary for implementing cross-site lookup columns.
• To expose a site list to a sister site or site higher in the site hierarchy, you must expose the source list as a site column configured at the site collection level.
• Working with Custom Content Types
• Custom content types added to the target list may not include additional lookup columns that you create later. 
• Using Additional Columns: 
• when you define a site or site collection column based upon a column in an existing list, you have the option of also making other columns available in that list as additional columns.  However, not all list columns will be usable in this way, from my experience.  For example, if you have custom columns, lookup columns, etc in the list, these will not be available (or even visible) when you select additional columns during the site column definition process.  Neither is the Choice column.
• There appears to be some disconnect within Microsoft articles on this very issue.  If you read the Microsoft article on this topic for 2013, you get one list of columns that can be used as additional columns.  However, if you read the earlier Microsoft article on this topic for 2010, you get a significantly reduced list of columns.  From my experience, Microsoft's 2010 version of this article is the more accurate one.