List lookup columns in SharePoint are great. Easy to setup, simple to use, and powerful. But they have some limitations that can be frustrating.

Let me paint you a picture..

You have a SharePoint list that contains information about a customer entity (yes it should probably be in CRM, but lets assume you don’t have one) – fields like contact names/numbers, addresses, unique systems, notes, etc. Some of these fields are single lines of text, pull-down menus, yes/no radio buttons, multiple lines of text, you name it.

You have another list that relates to sales of products to customers. Unsurprisingly, you want to link a sale to a customer, and you want to leverage the power of lookup columns to make that a simple and seamless process.

Not an unrealistic scenario. Sure there are better ways of doing it with the likes of webservices into CRM or BCS connections into LOB databases, but they all involve additional systems, coding skills, and generally more effort. All things that aren’t always readily available.

By adding a lookup column type to the sales list you can allow a customer entity to be selected from your customer list. Where this gets handier is you can have the sales list pull other values from the customer list without adding extra columns. Awesome.

But… not all the columns from your customer list are available. Why not?

SharePoint can only perform a lookup of values from columns that contain a ‘text’ value, and then only if it contains a single line of normal text (ie. “Single line of text”, a “number”, or “date”). Any field that contains multiple lines of text, other lookups, or multi-select items won’t be available to you, as SharePoint will automatically hide any columns that it knows it can’t return.

This same restriction applies to using these column types in calculated columns, and there is a great post by Dessie Lunsford on getting around this limitation in terms of calculated columns which you’ll find here – http://www.endusersharepoint.com/2009/06/17/taming-the-elusive-%E2%80%9Ccalculated-column%E2%80%9D-referencing-multiple-lines-of-text-column/

The workaround involves creating your problem field as a “single line of text” column, then creating a second calculated column that references the first column name – eg. [=ColumnName]. You then delete the first column and recreate it with the exact same name but this time selecting your column type of choice.

While Dessie’s post deals specifically with referencing these columns via calculated fields, by dint of good fortune and SharePoint consistency, the same workaround fixes the lookup problem as well. Thanks Dessie!

This issue applies to all versions of SharePoint since 2007, including SharePoint Online (BPOS/Office365)