Excel XLOOKUP for Text Contained Within a Cell
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.
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.
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.
Click the cell where you want the matching group code to appear.
Enter =XLOOKUP(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2)),$G$3:$G$6,"No match") and press Enter.
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 INDEX and XMATCH for the same partial-text lookup
Use this alternative when you prefer to separate the position search from the returned result or need the accepted INDEX/XMATCH pattern.
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. 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. Choose the result cell: Click the cell where you want the matched group code to display.
- 3. Enter the formula: Type =XLOOKUP(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2)),$G$3:$G$6,"No match") and press Enter.
- 4. Review and fill the formula: Check the returned code, then drag the fill handle down if you need to process additional rows.

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.




