Function Problems

How to Use BYROW Instead of MAP for a Spilled Excel Formula

WPS Editor Sep 27, 2026 871 views

Question details

The user wants to process each lookup value in an array and return a spilled list of comma-separated results using Excel formulas without relying on the MAP function.

How to Use BYROW Instead of MAP for a Spilled Excel Formula

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic, spilled array of comma-separated text values based on specific lookup criteria across multiple rows.
Observed behavior: The goal is to successfully join matching values from a dataset into a spilled output using BYROW and LAMBDA instead of MAP.

Before you start

Ensure that you are using a modern version of Excel or WPS Office that supports dynamic array functions like BYROW, LAMBDA, and FILTER.

Solution 1Recommended

Implement BYROW with LAMBDA and TEXTJOIN

This solution applies a custom LAMBDA function to each row in the lookup array, filtering the raw data and joining the corresponding matching results with a comma.

While the MAP function is excellent for applying a LAMBDA to individual elements, using BYROW is often safer when the nested function (like FILTER) returns an array. By strictly processing row by row, BYROW circumvents nested array calculation errors.

Select the target cell

Click on the top cell where you want the spilled array of comma-separated values to begin.

Enter the formula

Type =BYROW(K3:K9,LAMBDA(r,TEXTJOIN(",",,FILTER(D3:D54,E3:E54=r)))) into the formula bar. Replace K3:K9 with your lookup values, and D3:D54 / E3:E54 with your target data ranges.

Execute the formula

Press Enter. The formula will automatically spill down the column, returning the joined comma-separated values for each matched row.

Implement BYROW with LAMBDA and TEXTJOIN

Dynamic Spilling: Because this is a dynamic array formula, you do not need to drag it down manually; it will automatically populate adjacent cells as needed.

Advanced Spreadsheet Functions

Handle Dynamic Arrays Seamlessly in WPS Office

WPS Spreadsheet provides robust support for modern array formulas, text manipulation, and complex data lookups, making it easy to create spilled results without heavy manual formatting.

1. Open your workbook: Launch WPS Office and open your existing spreadsheet containing the raw data.
2. Locate the output cell: Select the empty cell where you want the dynamic spilled array to start.
3. Input the array formula: Enter your combined BYROW, LAMBDA, TEXTJOIN, and FILTER formula into the formula bar.
4. View the results: Press Enter to execute and watch the data seamlessly spill into the rows below.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports advanced text and lookup functions for complex data processing.Free and lightweight alternative with a familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my BYROW formula return a #CALC! error?

The #CALC! error typically occurs if the FILTER function within your LAMBDA returns an empty array. You can fix this by adding an [if_empty] argument to the FILTER function, such as FILTER(D3:D54,E3:E54=r,"No Match").

Why use BYROW instead of MAP for this specific scenario?

While MAP applies a LAMBDA to each individual value across multiple arrays, BYROW processes the array strictly row by row. This is often more intuitive and prevents nested array limitation errors when returning results from functions like FILTER that inherently generate arrays themselves.

Will this formula work in older versions of Excel?

No, dynamic array functions such as BYROW, LAMBDA, and FILTER are only available in Microsoft 365, Excel 2021, and newer modern spreadsheet platforms like WPS Office. Older versions will return a #NAME? error.

Excel & Data Problems