Function Problems

How to Create a Related-Records Report in Excel for the Web Without VBA

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user needs to combine unique entries from one worksheet with all related rows from a second worksheet.

How to Create a Related-Records Report in Excel for the Web Without VBA

Product: Excel for the web
Device & OS: not provided
Scenario: Creating a data report using a free personal OneDrive account, where VBA macros are not supported.
Observed behavior: Requires extracting multiple matching records from another sheet relying entirely on formulas like INDEX and MATCH.

Before you start

Ensure both your primary sheet and the related data sheet are organized with clear headers and contain a common identifying column (like an ID number) before writing your formulas.

Solution 1Recommended

Use the FILTER Function to Extract All Related Records

The FILTER function is the most efficient way to extract multiple matching rows in Excel for the web without VBA.

While traditional formulas only return a single value, Excel for the web supports dynamic array formulas. The FILTER function can search for a unique identifier and instantly return every corresponding row from your related dataset.

Select the target cell

Navigate to the blank sheet where you want to build your related-records report and click on the starting cell.

Enter the FILTER formula

Type =FILTER(Sheet2!A:Z, Sheet2!A:A = A2, "No match"), replacing 'Sheet2!A:Z' with your actual data range and 'Sheet2!A:A = A2' with the column and cell containing your matching identifier.

Press Enter to spill results

Hit Enter on your keyboard. Excel for the web will automatically spill all related rows into the adjacent empty cells, creating a full report instantly.

Use the FILTER Function to Extract All Related Records

Dynamic Array Support: If you add new rows to the source data that match the identifier, the FILTER function will automatically update the report with the new related records.

Solution 2

Use INDEX and MATCH for First Match Retrieval

Use the classic INDEX and MATCH combination if you only need the very first related record for each unique entry.

Create Advanced Data Reports Easily with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic arrays and advanced lookup functions like FILTER, INDEX, and MATCH. This allows you to smoothly generate complex related-records reports without relying on VBA coding or experiencing web-browser limitations.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the main report sheet and your related raw data sheet.
2. Enter the lookup formula: Select the target cell in your report and type =FILTER(Sheet2!A:C, Sheet2!A:A=A1, "No data").
3. Generate the report dynamically: Press Enter to apply the formula. WPS Spreadsheet will automatically spill all related records into the adjacent cells, creating your comprehensive report.

100% compatible with Microsoft Excel formulas and .xlsx filesFully supports advanced dynamic array functions like FILTERSmooth, offline desktop performance even with massive multi-sheet datasetsFree to download and use with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX and MATCH formula only return one row?

The MATCH function is designed to stop searching as soon as it finds the first match in an array. To retrieve all corresponding rows without VBA, you must use dynamic array formulas like FILTER.

Are dynamic array formulas supported in Excel for the web?

Yes, Excel for the web supports dynamic array formulas natively. Functions like FILTER, UNIQUE, and SORT will work perfectly in your free personal OneDrive account.

Can I use VBA macros in Excel for the web to run this report?

No, Excel for the web does not support VBA macros. You must rely on formulas like FILTER or use Office Scripts to automate and retrieve related data online.

Excel & Data Problems