# Fix the SharePoint lookup column threshold

> For the full documentation index, see https://250highstreet.com/llms.txt

> A SharePoint view that errors on lookup columns hit a limit of 12. See which columns count, how to find them, and how to trim a view to get under it.

Short answer: SharePoint stops a view or query that uses too many lookup-type columns. Microsoft's lookup help page says the List View Lookup Threshold "by default is twelve lookup columns". Person or Group columns count as lookup-type columns, according to a Microsoft support answer, and an archived Microsoft post lists Created By and Modified By too. Remove columns from the view, or split it into two views, to fix the error.

## What the error says

You may see a message like this when a list or library loads:

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

The text of the message can differ by screen. The cause is the same: too many lookup-type columns in the query. Microsoft's lookup help page says: "You have exceeded the List View Lookup Threshold, which by default is twelve lookup columns."

## Which columns count

It is not only columns called Lookup. A Microsoft support answer on Microsoft Q&A says: "The threshold limit of 12 lookup columns includes lookup columns and person columns." So a Person or Group column counts.

An archived Microsoft blog post, written for SharePoint 2010 and 2013, describes the threshold as the "maximum number of join operations, such as those based on lookup, Person/Group, or workflow status columns." The same post says standard lookup columns, single-value and multiple-value managed metadata, single-value and multiple-value people and group columns, Workflow Status, Created by and Modified by are counted. Treat that list as a guide: it describes older SharePoint versions.

The support answer adds: "It may be caused by some hidden lookup columns, such columns about flows and workflows." That means a list can seem to have fewer lookup-type columns than SharePoint counts. Our advice, not a Microsoft rule: when your count looks low but the error stays, look at the list settings for columns you did not add yourself.

## The limit is on the view or query, not on the list

The archived post says the feature "limits the number of joins that a query can perform" and counts the "Lookup, Person/Group, or Workflow Status fields that are included in the query". A list can hold more lookup-type columns than the limit. A view, or a flow's Get items call, that asks for more than the limit is what fails. The support answer gives the same direction for the fix: "Please reduce lookup columns in the view."

## How this differs from other guides

This guide is only about the 12 lookup-type column limit. For a list with more than 5,000 items, see "[SharePoint list over 5,000 items: what breaks and the fix](/guides/sharepoint-list-view-threshold/)". For a lookup that shows blank or too few fields, see "[SharePoint lookup column shows blank or too few fields](/guides/sharepoint-lookup-column-not-showing/)".

## The example in this guide

A team keeps a Requests list. Its columns are:

- Title (the request name)
- Customer (Lookup)
- Owner (Person)
- Requested by (Person)
- Approver (Person)
- Site (Lookup)
- Priority (Choice, with values 1 High, 2 Medium, 3 Low)
- Due date (Date and Time)

Over time the team adds more Person and Lookup columns. The default "All Items" view shows all of them. One day the view stops loading.

## Step 1. Count the lookup-type columns

1.  Open the list in your browser and select the gear icon, then List settings. Exact names may differ on your screen.
2.  Look at the column list. Mark every column of type Lookup.
3.  Mark every column of type Person or Group.
4.  Add the two counts together.

Also count the built-in Created By and Modified By columns if your view shows them. The archived Microsoft post lists both as counted.

If the total is more than 12, you have found the likely cause.

## Step 2. Open the view that fails

The limit applies to the query a view makes. A smaller view can work when the big one fails. This is why the fix is about the view, not only the list.

1.  Open the list.
2.  Select the view name at the top right, then pick the view that errors.
3.  Select Edit current view. Exact names may differ.

If you cannot open the view at all, open a different view first. Our advice, not a Microsoft rule: if no view loads, ask your admin for help before you delete anything.

## Step 3. Trim the columns in the view

1.  In the Edit view page, find the Columns section.
2.  Clear the check box next to each lookup-type column you do not need on this screen.
3.  Keep only the ones people use every day. In the example, keep Customer and Owner. Clear Requested by, Approver and Site.
4.  Select OK.

You have not deleted any data. You have only hidden the columns in this view. The values are still in each item.

## Step 4. Build a second view for the rest

If people need the hidden columns sometimes, make a second view.

1.  In the view menu, select Save view as, or Create new view. Exact names may differ.
2.  Name it "Approvals".
3.  Check the columns this group needs, such as Approver and Requested by. Keep the lookup-type count under 12.
4.  Select OK.

Now each group opens the view that fits their job. Neither view goes past the limit.

## Step 5. Cut columns you do not need

If you have columns nobody uses, remove them. Do this with care, because deleting a column deletes its data in that list.

1.  Export or back up the list first. See "[How to back up a SharePoint list before someone wrecks it](/guides/back-up-sharepoint-list/)".
2.  In List settings, open the column.
3.  Check that no view, form, rule or flow uses it.
4.  Select Delete.

One way to start: turn a lookup into a Choice column when the values are few and fixed. A Choice column is not a lookup-type column. Our advice, not a Microsoft rule: only do this for a short list of values that does not change.

## Step 6. Check your Power Automate flows and Power Apps

A Power Automate flow that reads the list makes a query too. In the SharePoint "Get items" action, the connector lists a field called "Limit Columns by View". Microsoft describes it as: "Avoid column threshold issues by only using columns defined in a view".

1.  Open [https://make.powerautomate.com](https://make.powerautomate.com) and open the flow.
2.  Open the Get items action.
3.  Under Advanced parameters, add Limit Columns by View.
4.  Type the name of the smaller view you built.
5.  Set Top Count. Microsoft says the default item limit is 100, and: "If you are working with large lists, you can increase this limit up to the list view threshold limit of 5,000."
6.  Under Settings, turn Pagination on if you need more rows than one page returns.

Save and test the flow. Exact names may differ on your screen.

For a Power Apps app on the same list, Microsoft's SharePoint connector page says an incomplete data set or inaccurate results from a SharePoint list might be caused by delegation limits. Check the delegation warnings in the app before you blame the lookup limit.

## What to avoid

- Do not add a new Person or Lookup column to fix a display problem. Each one counts toward the limit.
- Do not delete columns before you have a backup.
- Do not assume the cause is the 5,000 item limit. That is a different limit, and the fix is different.
- Do not put every column in the default view. A default view is the one most people open, so keep it small.

## When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: \$5,000 for one, built in 2 weeks. See "[What a SharePoint tracker costs to build](/guides/what-a-sharepoint-tracker-costs/)". Bring one example of the problem to a free call.

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: [Tools I built](/portfolio/)

## FAQ

### What is the lookup column limit in SharePoint Online?

Microsoft's lookup help page says the List View Lookup Threshold "by default is twelve lookup columns". It applies to a view or query, not to how many lookup columns the list can hold.

### Does a Person column count as a lookup column?

A Microsoft support answer on Microsoft Q&A says: "The threshold limit of 12 lookup columns includes lookup columns and person columns." So yes, it counts.

### Do Created By and Modified By count toward the limit?

An archived Microsoft blog post for SharePoint 2010 and 2013 lists Created by and Modified by among the counted columns. Count them when your view shows them.

### Does a calculated column count toward the limit?

The archived Microsoft post lists lookup, Person or Group, managed metadata and Workflow Status columns as counted. It does not list calculated columns.

### Why does my view fail when I count fewer than 12?

The support answer says: "It may be caused by some hidden lookup columns, such columns about flows and workflows." Check the list settings for columns you did not add, and trim the view.

## Sources

- [Microsoft Support, Create list relationships by using lookup columns](https://support.microsoft.com/en-us/sharepoint/lists/data-and-lists/create-list-relationships-by-using-lookup-columns)
- [Microsoft Learn, SharePoint connector reference](https://learn.microsoft.com/en-us/connectors/sharepointonline/)
- [Microsoft Learn, In-depth analysis into 'Get items' and 'Get files' SharePoint actions for flows in Power Automate](https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-get-items-and-get-files)
- [Microsoft Q&A, Microsoft support answer (2024-04-16)](https://learn.microsoft.com/en-us/answers/questions/1655727/the-query-cannot-be-completed-because-the-number-o)
- [Microsoft Learn archive, SharePoint 2010/2013: List view Lookup threshold uncovered](https://learn.microsoft.com/en-us/archive/blogs/spses/sharepoint-20102013-list-view-lookup-threshold-uncovered)

## Related guides

- [SharePoint list over 5,000 items: what breaks and the fix](/guides/sharepoint-list-view-threshold/)
- [Filter a view on a lookup column that allows several values](/guides/sharepoint-lookup-allow-multiple-values-filter/)
- [SharePoint lookup column shows blank or too few fields](/guides/sharepoint-lookup-column-not-showing/)

Source: https://250highstreet.com/guides/sharepoint-lookup-column-threshold/