Function Problems

How to Return a Category for Partial Text Matches in Excel

Ayan Masood Sep 28, 2026 870 views

Question details

The user wants to categorize data based on partial text matches by searching a cell's text against a keyword range on a different sheet and returning the corresponding category.

How to Return a Category for Partial Text Matches in Excel

Product: Excel
Device & OS: not provided
Scenario: Categorizing data by finding specific keywords within a text string using a separate reference table.
Observed behavior: By utilizing a combined LOOKUP and SEARCH formula, Excel can identify a keyword within a string and output the mapped category.

Before you start

Ensure your keyword mapping table and data table are clearly organized on their respective sheets, and verify there are no duplicate keywords in your reference range that could cause incorrect category returns.

Solution 1Recommended

Use LOOKUP and SEARCH Functions to Find Partial Matches

Combine the LOOKUP and SEARCH functions to check a cell against an entire list of keywords and return the corresponding category when a match is found.

The SEARCH function looks for the keywords within the target text and returns their positions. The LOOKUP function then processes these results to find the last valid match and returns the associated category from your designated category range.

Select the target cell

Click on the cell where you want the category to be displayed (for example, cell N423).

Input the formula

Enter the following formula: =LOOKUP(2,1/SEARCH('2023_Summary'!$G$33:$G$39,'Board Calendar 2022-2023'!C423),'2023_Summary'!$B$33:$B$39)

Adjust ranges to match your workbook

Modify the sheet names ('2023_Summary', 'Board Calendar 2022-2023'), the keyword range ($G$33:$G$39), and the category range ($B$33:$B$39) to match your actual data layout.

Apply and fill down

Press Enter to evaluate the formula. Once the category appears, use the fill handle to drag the formula down to apply it to other rows.

Use LOOKUP and SEARCH Functions to Find Partial Matches

Handling Multiple Matches: If multiple keywords match the target cell's text (e.g., if a board item contains both 'France' and 'Italy'), the formula typically returns the category corresponding to the last matching keyword in the lookup array. Duplicate keywords in the source range may also produce overlapping results.

Advanced Data Analysis

Categorize Data Effortlessly in WPS Spreadsheet

WPS Office provides a powerful Spreadsheet application that fully supports complex array formulas, including the LOOKUP and SEARCH combination, enabling you to extract and categorize data seamlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data and reference sheets.
2. Enter the categorization formula: Select the destination cell and input the LOOKUP and SEARCH formula, referencing your specific keyword and category ranges.
3. Apply to all rows: Press Enter to generate the category, then double-click or drag the fill handle to quickly categorize the rest of your data.

100% format compatibility with Microsoft Excel (.xlsx) filesFull support for advanced functions like LOOKUP, SEARCH, and XLOOKUPLightweight application that runs smoothly on older or lower-spec devicesFree to use with a familiar, easy-to-navigate user interface

microsoft office alternative - wps office

Frequently Asked Questions

What happens if a cell contains multiple keywords from the lookup range?

When using the LOOKUP and SEARCH combination, if multiple keywords from your reference list are found within the target cell, the formula will return the category associated with the last matched keyword in your reference array.

Can I use XLOOKUP for partial matches instead?

Yes, you can use XLOOKUP with wildcard characters (like the asterisk '*') to find partial matches. However, the LOOKUP and SEARCH combination is particularly useful when checking a single target cell against a large reference list of keywords, rather than checking one keyword against a large list of target cells.

Why does my formula return an #N/A error?

The formula will return an #N/A error if none of the keywords in your specified reference range can be found within the target cell's text. You can wrap the formula in an IFERROR function to display a custom message like 'Uncategorized' instead of an error.

Excel & Data Problems