# SharePoint lookup column shows blank or too few fields

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

> A SharePoint lookup column that shows blank or one field has a type or setup problem. A diagnosis checklist, the fixes, and when Choice is simpler.

A SharePoint lookup column shows blank or too few fields for these reasons: the source column is a type a lookup cannot use, such as Choice; you picked only one source column and need to select More options to show more; or the source list is on a different site. Check those in that order. If the values you need are a short, fixed list, a Choice column can be the simpler answer.

## What a lookup column does

A lookup column links two lists. One list holds the data, called the source list. The other list shows that data, called the target list. In the target list, a person picks a value from a drop-down, and SharePoint fills it in from the source.

Microsoft describes the pattern with a Course list as the source and a Reading list as the target. You make the source list first. Then you add a Lookup column to the target list. Exact names may differ on your screen.

## How this differs from other guides

This guide is about two lists that are linked and a column that shows blank or too little. For lists that get slow or block at 5,000 items, see "[SharePoint list over 5,000 items: what breaks and the fix](/guides/sharepoint-list-view-threshold/)". For a one-list ticket tracker, see "[Help desk ticket tracker in a SharePoint list](/guides/help-desk-tracker-sharepoint/)". For a list of vendors with dates, see "[How to set up a vendor list with renewal dates in SharePoint](/guides/setup-vendor-list-renewal-dates-sharepoint/)".

## The example in this guide

A team has two lists.

- Customers: the source list. Columns are Title (the customer name), Account number (Single line of text) and Region (Choice).
- Requests: the target list. Each request belongs to one customer. Columns are Title (the request), Customer (the lookup) and Priority (Choice, numbered).

The goal: when someone picks a customer on a request, the request also shows the account number and the region. Today the Customer column is blank, or it shows only the name.

## Diagnosis checklist

Go through these in order. Stop at the first one that explains what you see.

1.  Is the source column a type a lookup can use? Microsoft lists the types you can use as a source: Single line of text, Number, Date and Time, and Lookup (single value). Microsoft lists these types as not supported: Multiple lines of text, Choice, Calculated, Hyperlink or Picture, custom columns, Lookup (multi-valued), Person, Yes/No and Currency.
2.  Are both lists on the same site? Microsoft says lookup columns should be created and used within the same site.
3.  Did you bring in the extra fields? Microsoft says to select More options to display more lookup information from the source list in more columns. Check the source columns you also want.
4.  Is the source column the one you want? A lookup shows one source column. If it is not Title, the names in the drop-down will not be the names you expect.
5.  How many lookup columns are in the view? Microsoft's message reads "You have exceeded the List View Lookup Threshold, which by default is twelve lookup columns."
6.  Is the source list empty or very large? A drop-down with no choices means the source list has no items to pick from. For very large lists, see the list view threshold guide linked above.

## Fix 1. The source column is the wrong type

In the example, Region is a Choice column. A lookup cannot use a Choice column as its source. So Region will not appear in the list of columns to pick from.

Here are two ways out. Pick the one that fits your data.

1.  Keep the data as it is and rebuild the source column as a Single line of text. Then type the region in each customer row.
2.  Copy the Choice values into your target list as a Choice column of its own. This is the simpler answer when the values are few and rarely change. See "When a Choice column is simpler" below.

Account number is a Single line of text column, so it can be used as a source.

## Fix 2. Add the missing fields

This is one cause of "too few fields". You added the lookup and picked Title. Only the name shows. You also wanted the account number.

Microsoft describes the fix in "[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)". In short:

1.  Open the Requests list and select Add column, then Lookup. If the column already exists, open its settings to edit it.
2.  Under "Select a list as a source", pick Customers.
3.  Under "Select a column from the list above", pick Title.
4.  Select More options.
5.  Check the box for Account number.
6.  Select Save.

Microsoft says "You can add more columns in these settings later". After you save, picking a customer should fill in the account number.

Only columns of the supported types will be offered. Region, a Choice column, will not be there. That is Fix 1.

## Fix 3. Move the lookup to the same site

If the Customers list is on a different site, the lookup cannot use it. Microsoft says the lookup column and its source should be on the same site. Site columns made at the root site cannot be used on subsites.

One way to start: move or copy the Customers list to the site where Requests lives, then rebuild the lookup. Check with your admin if the sites are managed by another team.

## Fix 4. Too many lookup columns in one view

Microsoft's message reads "You have exceeded the List View Lookup Threshold, which by default is twelve lookup columns." Microsoft does not give a step-by-step fix for this threshold on that page. These are ways to stay under it:

1.  Open the Requests list and count its lookup columns.
2.  Remove any lookup column that nobody uses.
3.  Turn a lookup into a Choice column where the values are few and fixed.
4.  Keep extra lookup fields in a separate list if the list keeps growing.

The same Microsoft page also says some actions are blocked when a list has more items than the List View Threshold. Examples are making a column unique and turning on delete rules for a lookup. For the 5,000 item limit, see the threshold guide linked above.

## Fix 5. Items missing from the drop-down

If a customer is not in the drop-down, check the Customers list first. The lookup can only show items that exist in the source list.

Also check what happens when a customer is deleted. Microsoft says that, under Relationship, you can check "Enforce relationship behavior" and choose Restrict delete or Cascade delete.

- Restrict delete: source items that are used in the target list cannot be deleted.
- Cascade delete: deleting a source item also deletes the related items in the target list.

If "Enforce relationship behavior" is not checked, Microsoft says deleting a source item removes it from the target list by default. A blank Customer on old requests can mean the customer row was deleted. One way to start is to choose Restrict delete, so a customer in use cannot be removed by mistake. You need Manage Lists permission to change this. If you do not see it, check with your admin.

## When a Choice column is simpler

A lookup is for values that live in their own list and change. A Choice column is for a short, fixed set. Microsoft's page also notes that to limit values in a column to those stored on the site, you use a Lookup instead of a Choice. The reverse also holds: if the values do not need to be shared with anything else, a Choice column does the job.

Our advice, not a Microsoft rule: use a Choice column when:

- There are about ten values or fewer.
- The values change seldom.
- You do not need extra fields next to each value.
- Nobody else needs to add, edit or delete the values.

Use a lookup when:

- The values are rows that people add and change, such as customers.
- You want to show more than one field, such as account number next to the name.
- Other lists use the same values.

In the example, Priority stays a Choice column: 1 High, 2 Medium, 3 Low. The numbers in front make the stored text exact. Customer stays a lookup, because customers change and carry an account number.

If a Choice column works for a lookup you already have, one way to start is to add the Choice column first, copy the values across, check the list, and only then remove the lookup.

## Worked example, start to finish

Follow these steps to fix the example list.

1.  In Customers, check each column type. Title is Single line of text. Account number is Single line of text. Region is Choice.
2.  Add a Single line of text column named Region name. Copy the region from each Choice value. Keep the Choice column for now.
3.  In Requests, open the Customer lookup settings.
4.  Set the source list to Customers and the source column to Title.
5.  Under More options, check Account number and Region name.
6.  Under Relationship, check Enforce relationship behavior, and pick Restrict delete.
7.  Select Save.
8.  Open a request and pick a customer. The account number and the region name should fill in.
9.  Add a Priority Choice column with 1 High, 2 Medium, 3 Low.
10. Make a view called "By customer" that shows Title, Customer, Account number, Region name and Priority.

Test with one real row before you tell the team. Exact names may differ on your screen.

## What to avoid

- Do not pick a Choice, Person, Yes/No, Currency or Calculated column as the lookup source. They are not supported.
- Do not add every source column under More options. Add only what people read.
- Do not build a lookup to a list on another site.
- Do not delete source rows without a delete rule. Customers in use may go blank on old requests.
- Do not add more lookup columns than you need to one list. The default limit is twelve.
- Do not use a lookup for a short fixed list. A Choice column is simpler.

## Honest limits

A lookup keeps two lists in step, but it does not check that the data is right. If the account number is typed wrong in Customers, it is wrong everywhere. Also, a lookup can use only the supported column types, so some data has to be rebuilt as text. If you need many links between many lists, a database or a custom app may fit better. For a small or middle team, one source list and one or two lookups may be enough.

## 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

### SharePoint lookup column not showing all fields?

Edit the lookup column, select More options, and check each source column you also want. Microsoft says "You can add more columns in these settings later". Exact names may differ on your screen.

### SharePoint lookup column with a Choice source column?

Microsoft lists Choice as not supported as a lookup source. Rebuild the source as a Single line of text, or use a Choice column in the target list.

### SharePoint lookup column from another site?

Microsoft says "Lookup columns should be created and used within the same site." Move or copy the source list to the target's site, then rebuild the lookup.

### SharePoint lookup column with a multi-valued Lookup as the source?

Microsoft lists Lookup (multi-valued) as not supported as a source column type. Supported types are Single line of text, Number, Date and Time, and Lookup (single value).

### How many lookup columns can a view have?

Microsoft's message reads "You have exceeded the List View Lookup Threshold, which by default is twelve lookup columns." Remove lookups you do not use, or switch fixed lists to Choice columns.

## 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 Support, List and library column types and options](https://support.microsoft.com/en-us/sharepoint/lists/data-and-lists/list-and-library-column-types-and-options)
- [Microsoft Support, Create a column in a list or library](https://support.microsoft.com/en-us/office/create-a-column-in-a-list-or-library-2b0361ae-1bd3-41a3-8329-269e5f81cfa2)

## Related guides

- [Fix the SharePoint lookup column threshold](/guides/sharepoint-lookup-column-threshold/)
- [Filter a view on a lookup column that allows several values](/guides/sharepoint-lookup-allow-multiple-values-filter/)
- [SharePoint list version history is blank or missing](/guides/sharepoint-list-version-history-not-showing/)

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