Function Problems

Excel XLOOKUP for Text Contained Within a Cell

Bushra ParveenBushra Parveen Nov 12, 2026 868 views

Question details

The user needs to return a matching three-letter group code when a cell contains the code within additional text.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A reference table contains group codes such as ABC, DEF, GHI, and JKL, while the lookup cell contains one or more codes with extra characters or text.
Observed behavior
An exact XLOOKUP does not find a result because the lookup cell is not identical to the reference value.
Before you start

Confirm that the reference group codes are listed in G3:G6 and that the text to search is in B2. Arrange the reference codes in the priority order you want returned when more than one code appears in the cell.

Solution 1Recommended

Use SEARCH with XLOOKUP to match text inside a cell

Use this dynamic-array formula when the reference code can appear anywhere inside the target cell and you want the first matching code returned.

SEARCH checks every reference code against the text in B2. ISNUMBER converts each successful search into TRUE, and XLOOKUP returns the first reference value associated with TRUE.

1
Select the result cell

Click the cell where you want the matching group code to appear.

2
Enter the partial-match formula

Enter =XLOOKUP(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2)),$G$3:$G$6,"No match") and press Enter.

3
Check the returned code

Review the result. If B2 contains a code such as DEF-1 or other surrounding text, the formula returns DEF when DEF is listed in G3:G6.

Use SEARCH with XLOOKUP to match text inside a cell
Match priority: If multiple reference codes occur in B2, XLOOKUP returns the first match in the order shown in G3:G6.

Match Embedded Group Codes in WPS Spreadsheet

WPS Spreadsheet can use the same SEARCH and XLOOKUP logic to find a group code embedded in longer cell text. Enter the formula in a compatible spreadsheet and return the first matching reference value without changing the source text.

  1. 1. Open the spreadsheet: Open the workbook in WPS Spreadsheet and confirm that the source text is in B2 and the reference codes are in G3:G6.
  2. 2. Choose the result cell: Click the cell where you want the matched group code to display.
  3. 3. Enter the formula: Type =XLOOKUP(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2)),$G$3:$G$6,"No match") and press Enter.
  4. 4. Review and fill the formula: Check the returned code, then drag the fill handle down if you need to process additional rows.
Finds codes contained within longer text instead of requiring an exact matchSupports a clear SEARCH and XLOOKUP workflow for repeated lookupsWorks with Microsoft Excel .xlsx files for easy file sharing and migrationOffers a lightweight spreadsheet interface for formula-based data cleanup
microsoft office alternative - wps office

Frequently Asked Questions

Why does an exact XLOOKUP fail when the cell contains DEF-1?

Exact XLOOKUP compares the complete lookup value with the complete cell value. Because DEF-1 includes extra characters, it does not equal DEF. SEARCH checks whether DEF appears anywhere in the cell.

What formula returns the first group code found inside B2?

Use =XLOOKUP(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2)),$G$3:$G$6,"No match"). The first TRUE result corresponds to the first matching code in G3:G6.

How can I avoid an error when there is no matching code?

Add a not-found result to XLOOKUP, as in =XLOOKUP(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2)),$G$3:$G$6,"No match"). For INDEX/XMATCH, use IFERROR around the formula.

What happens if B2 contains more than one reference code?

The formula returns the first match according to the order of the reference values in G3:G6. Reorder that range if a different priority is required.