Function Problems

How to Extract and Display Grouped Excel Data on Matching Project Worksheets

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to extract specific material descriptions and quantities from a central master worksheet and display them on individual project worksheets based on a unique project identifier (e.g., 'HANSE'), while ensuring the destination list expands dynamically as new rows are added.

Product: Excel
Device & OS: not provided
Scenario: Organizing and distributing inventory or material data from a main list into specific project tabs using unique identifiers.
Observed behavior: Data dynamically populates and expands on the destination project worksheet when matching records are found in the source master sheet.

Before you start

Ensure your central master worksheet has clear headers and consistent unique identifiers (like project names or codes) in a dedicated column before setting up your extraction formulas.

Solution 1Recommended

Use the FILTER Function for Dynamic Extraction (Recommended)

For Microsoft 365, Excel 2021, or WPS Office, the FILTER function is the most efficient way to extract and dynamically update grouped data.

The FILTER function automatically spills results into adjacent cells, meaning you do not need to manually drag formulas down. Combined with the SORT function, it can also automatically organize your project lists.

Select the Destination Cell

Navigate to your project worksheet and click on the cell where you want the first material description to appear (e.g., K21).

Enter the FILTER Formula

Type the formula =FILTER(Materials!B:C, Materials!A:A="HANSE", "No Data") into the formula bar. Replace 'Materials!B:C' with the columns containing your material descriptions and quantities, and 'Materials!A:A' with the column containing the unique project identifiers.

Apply and Review Spilled Data

Press Enter. The results will automatically spill down and across to populate the matching material descriptions and quantities for that specific project identifier.

Pro Tip: Wrap your FILTER formula in a SORT function, like =SORT(FILTER(...)), to automatically alphabetize the extracted materials list on your project worksheet.

Solution 2

Use INDEX and SMALL Functions for Older Versions

If you are using an older version of Excel that does not support dynamic array functions, use a combination of INDEX, MATCH, and SMALL formulas.

Efficient Data Extraction in WPS Office

Easily Filter and Display Grouped Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER and SORT, allowing you to instantly pull project-specific data from master lists without relying on complex, outdated array formulas.

1. Open Your Master File: Launch WPS Spreadsheet and open the workbook containing your central master worksheet and project worksheets.
2. Navigate to the Destination Tab: Switch to the specific project worksheet where you want the grouped data to be displayed.
3. Apply the FILTER Function: Type the =FILTER() function, referencing your master data sheet and the unique project identifier. Hit Enter to instantly generate a dynamic, self-expanding list.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Natively supports dynamic array functions like FILTER, SORT, and UNIQUE.Lightweight software that processes large interconnected data sets smoothly.Free and intuitive interface matching standard professional spreadsheet software.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDEX and SMALL formula returning an error or incorrect data?

Array formulas using INDEX and SMALL in older Excel versions must be entered by pressing Ctrl+Shift+Enter. Additionally, ensure your cell references in the master worksheet are locked (using $ symbols, like $A$1:$A$100) so the ranges don't shift when you copy the formula down.

Will the FILTER function update automatically when I add new materials to the master sheet?

Yes. If you reference entire columns (e.g., A:A) or, better yet, convert your master data into an official Table, the FILTER function will dynamically include new rows that match your unique identifier without requiring formula adjustments.

How do I handle #CALC! or #N/A errors when no matching project data is found?

If you are using the FILTER function, you can use its built-in third argument [if_empty] to specify a fallback text, such as =FILTER(range, condition, "No materials found"). If using INDEX and SMALL, wrap your entire array formula in an IFERROR function.

Excel & Data Problems