← Al Davies, 250 High Street

Filter a view on a lookup column that allows several values

Microsoft's pages do not show a way to filter a view on a lookup column that allows several values, so test any direct filter on a copy of the list first. If rows with several picks go missing, add a helper column that holds one value per row, and filter on that.

A lookup column that allows several values shows its picks together in one cell, separated by a semicolon. Before you change the "allow multiple" setting on any column that has data, back up the list. A community thread reports that turning the setting off can drop all but the first saved value.

What this page covers

This page is about one narrow problem. You have a lookup column, you turned on the setting that lets people pick more than one value, and now a view filter does not give the rows you expect. If your lookup column is blank or shows too few fields, read "SharePoint lookup column shows blank or too few fields" first. This page starts after that is fixed.

The example in this guide

A team has two lists.

The Jobs list has these columns:

The goal: a view called "Jobs for one customer" that shows every job where a chosen customer is one of the picks.

What Microsoft's pages say, and what they do not

Here is what Microsoft's pages cover.

Microsoft's pages do not describe how to filter a view on a lookup column that allows several values. They also do not describe an OR or AND setup for it. Treat everything in the next sections as something to test on your own list, not as a Microsoft promise.

Step 1. Back up the list before you change anything

You may be tempted to turn "allow multiple values" off and on again. Do not do that first.

A Microsoft Community Hub thread describes a list where a multi-value lookup column began saving only the first pick. One reply suggests turning "Allow multiple values" off, saving, turning it on again, and saving. The same reply warns that this can drop all but the first stored value for existing items. That reply is marked as the solution in the thread. It is one reply, not a Microsoft statement, and it names no single cause.

So make a backup first. For the options, see "How to back up a SharePoint list before someone wrecks it". One way to start:

  1. Export the list to Excel so you have a copy of every pick.
  2. Make a copy of the list with its data, or save a list template without data and keep the Excel file.
  3. Check that the copy opens and that the Customers column shows all the picks.

Only then change settings. Work on the copy when you can.

Step 2. Try the filter in the classic view editor

This is a test, not a sure fix. Do it on the copy of the list.

  1. Open the list. Select the view name at the top right, then choose Save view as. Name it "Jobs for one customer."
  2. Select the view name again and choose Edit current view. This opens the classic view editor.
  3. Scroll down to Filters.
  4. Choose "Show items only when the following is true."
  5. Set the first statement: Customers is equal to the customer name you want.
  6. Select OK and look at the rows.

Check the result against the real data. Open three jobs that you know include the customer, and see if they show. Then open a job with two customers, one of them the customer you chose, and see if it shows. If the rows with several picks are missing, the filter does not read the whole cell, and you should go to Step 4.

Microsoft's help page for this editor is "Use filtering to modify a SharePoint view" in the sources. Exact names may differ on your screen.

Step 3. Try OR statements for a short list of customers

If you want rows that match any of a few customers, you can add one statement per customer and join them.

  1. In the classic view editor, under Filters, set the first statement to Customers is equal to the first customer.
  2. Add a second statement and choose Or. Set it to the second customer.
  3. Repeat for each customer, up to the limit.

Microsoft's filtering page says that if you have Or statements, you specify them first, then add any needed And statements. It also says a filter can have up to 10 statements. To also hide finished work, add one last And statement: Status is not equal to Done. Our advice, not a Microsoft rule: use the classic view editor for "is not equal to" filters too.

AND joins narrow the result, and OR joins widen it. Microsoft's page says to use And when you combine columns so the first column limits the results. A job that has two customers will not match an AND of those two customers unless the filter reads inside the cell. That is exactly the part Microsoft's pages do not describe, so test it with a job you made for the purpose.

Step 4. Use a helper column that holds one value

When the filter on the multi-value column does not give the rows you need, one way to start is to stop filtering on that column. Add a column that holds a single value for each row, and filter on that.

Option A: a main customer column.

  1. In Jobs, add a second lookup column named "Main customer", to the Customers list, with only one value allowed.
  2. Ask people to pick the main customer there, and any others in Customers.
  3. Make the view filter on Main customer is equal to the customer name.

This is simple. The cost is that a job shows only under its main customer, not under every customer.

Option B: a text copy of the picks. This is our advice, not a Microsoft rule.

  1. Add a Single line of text column named "Customer list."
  2. Build a small Power Automate flow with the trigger "When an item is created or modified."
  3. Add a Select action. From: the Customers value from the trigger. Map: item()?['Value'].
  4. Add a Condition: Customer list is not equal to join(body('Select'), '; '). If no, stop. This keeps the flow from running itself again and again. This is our advice, not a Microsoft rule.
  5. In the yes branch, add Update item. Id: the trigger's ID. Title: the trigger's Title. Update item asks for it when Title is required. Customer list: join(body('Select'), '; ').
  6. In the view, filter Customer list with "contains" and the name you want.

The Condition in step 4 stops the loop. Test it with one item and read the run history. If you later add a second flow that reads many rows with a Get items action, set Top Count and turn on Pagination. The default returns only 100 rows. See "Power Automate Get items stops at 100 rows: how to get them all".

Option C: one row per customer. If each pair of job and customer needs its own status, make a third list for the pairs. Each row has one job lookup and one customer lookup, both single value. Then filter that list on the customer. This takes more setup, and it is the cleanest design for a growing list.

If the multi-value column stopped saving

If your column began to save only the first pick, here is what the community thread describes. It is one report, not a Microsoft statement.

One way to start is to make the new column next to the old one, copy the values across for a few rows, check them, and only then copy the rest. Keep the old column until you are sure.

Choice or lookup for several values

Microsoft's filtering page lists multi-valued Choice columns under unsupported column types for indexing and filtering, and lists Lookup as supported. So a Choice column that allows several picks is the weaker base for a filtered view. A lookup column keeps the names in one source list, which means a typo is fixed in one place.

How to check your result

  1. Pick three customers. For each one, count the jobs you know include that customer.
  2. Open the view for each customer. Count the rows.
  3. The two counts must match. If the view shows fewer, rows with several picks are missing.
  4. Add one test job with two customers. Check that it shows under both.
  5. Share the view only after the counts match.

What to avoid

Honest limits

A helper column adds work. People must fill in two places, or you must keep a flow running, and flows can fail or be turned off. A multi-value lookup is good for showing which customers belong to a job. It is a weaker base for reports and filters. If your team filters by customer all day, the third-list design in Option C can be the better long-term choice.

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". Bring one example of the problem to a free call.

Tell me what's still manual

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

FAQ

Can I filter a SharePoint view on a lookup column that allows several values?

Microsoft's pages do not describe it. Try an "is equal to" filter in the classic view editor on a copy of the list, and count the rows against known data. If rows with several picks are missing, use a helper column.

Which is easier to filter, a multi-value Choice column or a lookup column?

Microsoft's filtering page lists multi-valued Choice under unsupported column types for indexing and filtering, and lists Lookup as supported.

Is it safe to turn "allow multiple values" off and on again?

Not without a backup. A community thread warns that this can drop all but the first stored value for existing items. Export the list and copy it first.

How many statements can a classic view filter have?

Microsoft's filtering page says a filter definition can have up to 10 statements.

Does an index make this filter faster on a big list?

Microsoft says using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another column type for the index.

Sources