Function Problems

How to Find Multiple Text Matches Between Excel Columns

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to find and extract multiple text string matches (such as location names) from one column that appear within long descriptions in another column, outputting all matched results into a new column.

Product: Excel
Device & OS: not provided
Scenario: Extracting multiple specific keywords from a column of long text descriptions and combining the matches.
Observed behavior: The user wants to return all matched keywords as a combined string in a single cell corresponding to each description, rather than just returning the first match found.

Before you start

Ensure you are using a spreadsheet version that supports modern dynamic array formulas, such as REDUCE, LAMBDA, and TEXTSPLIT, as these are required for advanced multi-result extraction.

Solution 1Recommended

Extract Multiple Matches Using Dynamic Array Formulas

Utilize a combination of REDUCE, LAMBDA, TEXTSPLIT, and XLOOKUP to identify and return all matched text strings from your target keyword list.

This method splits the description text into individual words, checks each word against your keyword list, and combines any successful matches into a single cell using dynamic arrays.

Define your data ranges

Identify the column containing your descriptions (e.g., A2:A3) and the column containing your target keywords (e.g., $I$2:$I$5).

Enter the dynamic array formula

In the first cell of your output column, type the following formula: =DROP(REDUCE("",A2:A3,LAMBDA(a,I,VSTACK(a,ARRAYTOTEXT(TOROW(XLOOKUP(TEXTSPLIT(I," "),$I$2:$I$5,$I$2:$I$5),3))))),1)

Adjust references and execute

Modify A2:A3 to match your actual description column range, and change $I$2:$I$5 to precisely match your exact keyword list range. Press Enter to spill the results down the column.

Text Splitting Limitations: Because TEXTSPLIT uses a space delimiter here, multi-word keywords or punctuation attached to words may require additional data cleanup for exact matches.

Advanced Data Processing

Easily Process Complex Data and Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, making it easy to extract text, find matches, and process thousands of rows of data efficiently without lag.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your descriptions and keyword lists.
2. Select the target cell: Click on the cell where you want the extracted text matches to appear.
3. Apply the array formula: Paste the dynamic array formula using REDUCE and XLOOKUP, then press Enter to automatically spill the matched results.

Fully compatible with Microsoft Excel formulas and array functions.Handles complex text extraction and large-scale data processing smoothly.Free, lightweight, and features a familiar user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Can I match multi-word phrases using this formula?

By default, using TEXTSPLIT with a space delimiter splits descriptions into single words, which works best for single-word matching. For multi-word phrases, you may need to use SEARCH or FIND functions wrapped in an array instead of TEXTSPLIT.

Why is the formula returning a #NAME? error?

This error occurs if your spreadsheet software version does not support modern dynamic array functions like LAMBDA, REDUCE, or TEXTSPLIT. Ensure your software is updated to the latest version that supports these functions.

How do I remove punctuation from the matched results?

You can nest the SUBSTITUTE function inside your formula to strip out common punctuation marks, such as commas or periods, before the TEXTSPLIT function evaluates the description.

Excel & Data Problems