Create an Excel Project List for a Selected Company ID
Question details
Create a company ID drop-down and display all project names associated with the selected company.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains company IDs in column A, project names in row 1, and Y markers showing which projects belong to each company.
- Observed behavior
- The user wants the project list to update automatically when a different company ID is selected.
Confirm that each company ID appears once in the source table and that project assignments use a consistent marker such as Y. The formula below assumes project headers are in B1:ZZ1 and assignment markers start in B2.
Use FILTER to return projects for the selected company ID
Use this method in Microsoft 365, Excel 2021, or another version that supports the FILTER dynamic-array function.
Create a drop-down for the company selection, then use MATCH and INDEX to retrieve that company's assignment row. FILTER returns only the project headers whose corresponding marker is Y.
On the report sheet, select cell A1, then choose Data > Data Validation. Set Allow to List and select a range or named range that contains the available company IDs.
On the Project Sheet worksheet, keep company IDs in Project Sheet!A2:A1000, project names in Project Sheet!B1:ZZ1, and each company's Y assignment markers in Project Sheet!B2:ZZ1000.
Select the first output cell, such as B1 on the report sheet, and enter: =FILTER('Project Sheet'!$B$1:$ZZ$1,INDEX('Project Sheet'!$B$2:$ZZ$1000,MATCH($A$1,'Project Sheet'!$A$2:$A$1000,0),0)="Y","")
Choose a company ID from the drop-down in A1. The formula spills the matching project names across adjacent cells automatically.
Adjust the formula ranges for your worksheet
Use this option when your company IDs, project headers, or assignment table are stored in different rows or columns.
Build a Dynamic Company Project List in WPS Spreadsheets
WPS Spreadsheets lets you create a company ID drop-down and use FILTER, MATCH, and INDEX to return the projects assigned to that company. The result updates automatically when the selected ID changes.
- 1. Open the workbook in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet where the company ID will be chosen.
- 2. Add the company ID drop-down: Select the selection cell, such as A1, then use Data > Data Validation to create a List from the company ID range or named range.
- 3. Choose the project list output cell: Click the cell where the project names should begin, such as B1, leaving enough empty cells to the right for the spilled result.
- 4. Insert the FILTER formula: Enter the FILTER formula that matches A1 against the company ID column and returns headers with a Y marker in the matched row.
Frequently Asked Questions
Why does the FILTER formula return no project names?
Check that the selected company ID in A1 exactly matches a value in Project Sheet!A2:A1000 and that the assignment row contains Y values with no unexpected spaces or different text.
Can the project names be returned vertically instead of across a row?
Yes. Wrap the formula with TRANSPOSE, for example: =TRANSPOSE(FILTER('Project Sheet'!$B$1:$ZZ$1,INDEX('Project Sheet'!$B$2:$ZZ$1000,MATCH($A$1,'Project Sheet'!$A$2:$A$1000,0),0)="Y",""))
What does the MATCH function do in this formula?
MATCH finds the row number of the company ID selected in A1 within Project Sheet!A2:A1000. INDEX then returns that company's row of assignment markers for FILTER to evaluate.
How do I include a company ID drop-down from another worksheet?
Create a named range for the company ID cells using Formulas > Name Manager, then use that name as the Source in Data > Data Validation > List.
What Excel versions support this project list formula?
FILTER is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, use a helper-column approach, Power Query, or an array formula alternative.
