How to avoid Lookup Column Threshold limit on a view

A while back, I published a post on some most famous and important limits we have in SharePoint Online. These were limits that are most likely to be encountered by the users. Today, I want to introduce you to another limit that exists in SharePoint. Most of you will never experience it; however, for those who use lists or metadata on document libraries – you better not skip this article and read it to the end. Otherwise, you will join a very disappointed percentage of my blog readers after encountering the Lookup Column Threshold limit on a view and then desperately finding this post by googling for a solution. 😊

What is the Lookup Column Threshold limit on a view?

When you create lookup columns on a list or library, for performance reasons, Microsoft limits you to 12 (twelve) columns of that type of column in a single view. This is because when it is a lookup column, you are getting the data from either another list or another source. When you exceed the limit, you will get the following error message when trying to add the 13^th column to the view (this just proves that 13 is not a lucky number).

Something went wrong. The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.

And when you refresh the page and try to go to the document library, your library will go blank with the error message front and center.

What are lookup columns?

What is interesting here is that the lookup column in the content of this error message is not the same lookup column I blogged about some time ago.  In that post, I referred to the Lookup column type that we have in Lists and Libraries. Those Lookup columns were columns that referenced other lists and libraries on the same site.

In the context of the Lookup Column Threshold limitation, Lookup Column also refers to other types of columns! By lookup columns here, we imply the columns that obtain their data from other sources. Here is a complete list of the “lookup” columns that will cause the error message:

System Columns (Columns created and displayed out of the box)

• Created By Column (lookup against User/Employee Directory)
• Modified By Column (lookup against User/Employee Directory)
• Type Colum (Word, Excel, PowerPoint, PDF, etc.)
• Name Column (Filename)

Manual Columns (Columns created manually by users)

• Lookup Column (the actual Lookup column, lookup against another list or library)

• People Column (lookup against User/Employee Directory)
• Managed Metadata Column (lookup against the Term Store)
• Enterprise Keywords Column (lookup against the Term Store)

How to avoid the Lookup Threshold limit on a view

• The only way to avoid this issue is to limit your views to less than 12 lookup column types mentioned above. To be clear, you can have as many columns as you wish on a list or library, as long you do not exceed the 12 “lookup” ones in a single view. So, if you encounter the above limit, just hide some of the “lookup” columns from a view to fix it.
• Where appropriate, use other types of columns instead of the lookup columns (i.e., Choice instead of Managed Metadata) since those do not count against the limit
• If you do have many columns you need to display – create additional views on a list or library – just keep the number of “lookup” columns under 12 in any given view.