Function Problems

How to Find Matching Filenames and Combine Them in Excel

Algirdas Jasaitis Sep 27, 2026 869 views

Question details

The user wants to identify multiple image filenames containing specific article numbers and combine all matching results into a single cell using a semicolon separator.

How to Find Matching Filenames and Combine Them in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing and cross-referencing a large list of image files with their corresponding article numbers in a spreadsheet.
Observed behavior: The goal is to automatically aggregate multiple matching text values into one cell separated by delimiters, eliminating the need to manually search and copy the filenames.

Before you start

Ensure you are using Microsoft 365, Excel 2021, or a modern spreadsheet tool that supports dynamic array functions like FILTER, BYROW, and LAMBDA, as these formulas will not work in older Excel versions.

Solution 1Recommended

Combine Multiple Matches Using BYROW and FILTER

Use this dynamic array formula when you have a list of article numbers and want to return all matching filenames for the entire column in one go.

The BYROW function evaluates each article number in your selected column, while LAMBDA passes it into a FILTER array. ARRAYTOTEXT then combines the results. This is highly efficient for bulk processing.

Identify your source data ranges

Locate the column containing your article numbers (e.g., A2:A14) and the column containing the picture filenames (e.g., D2:D11).

Enter the dynamic array formula

Click on cell B2 and type: =BYROW(A2:A14,LAMBDA(a,ARRAYTOTEXT(FILTER($D$2:$D$11,ISNUMBER(SEARCH(a,$D$2:$D$11)),""))))

Apply the calculation

Press Enter. The formula will automatically spill down the column, finding partial matches using SEARCH and returning a combined list of matching filenames for each article number.

Combine Multiple Matches Using BYROW and FILTER

Delimiter Note: ARRAYTOTEXT defaults to a comma and space separator. If strict semicolons are required, replace ARRAYTOTEXT with TEXTJOIN(";",TRUE,[array]).

Solution 2

Use TEXTJOIN and FILTER for Specific Cells

This formula is ideal if you are evaluating a single article number or prefer to drag a formula down manually, ensuring exact semicolon delimiters.

Advanced Functions in WPS Spreadsheet

Quickly Combine Matching Data Using WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like TEXTJOIN, FILTER, and BYROW. You can easily process text-matching and data consolidation without paying for expensive software subscriptions.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your article numbers and filenames.
2. Select the result cell: Click on the cell where you want to output the combined semicolon-separated list.
3. Enter the formula: Input the TEXTJOIN and FILTER formula as you would in Excel to aggregate matching filenames.
4. Fill the series: Press Enter to calculate the result, then drag the fill handle down to apply it to all rows instantly.

Natively supports advanced array functions for complex data filtering and combining.Fully compatible with Microsoft Excel formats (.xlsx, .xls) and formulas.Lightweight, fast, and completely free to use for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

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

The #CALC! error typically occurs if the FILTER function doesn't find any matching filenames and the 'if_empty' argument is missing. Ensure your formula ends with ,"") so it returns a blank cell instead of an error when no matches exist.

Can I separate combined filenames with a semicolon instead of a comma when using BYROW?

Yes. While ARRAYTOTEXT automatically uses a comma and space, you can substitute it with the TEXTJOIN function. Your formula would look like this: =BYROW(A2:A14,LAMBDA(a,TEXTJOIN(";",TRUE,FILTER(...)))).

Why do the BYROW and FILTER functions return a #NAME? error?

A #NAME? error means your spreadsheet software does not recognize the function. Functions like BYROW, LAMBDA, and FILTER are dynamic array functions available only in newer software like Excel 2021, Microsoft 365, or modern versions of WPS Office. Upgrading your software will resolve this.

Excel & Data Problems