How to Xlookup Match Text Contains in Excel

Why This Task Matters in Excel

Have you ever needed to pull information from a master list when you don’t know the exact spelling of the item you are searching for? Perhaps you only have a fragment of a customer name, a single keyword that appears somewhere in a project title, or part of a Stock Keeping Unit (SKU) buried inside a longer code. In day-to-day business this happens constantly:

• A customer support agent must retrieve a full order record when the caller only remembers “Lightweight Jacket” in the 200-item product name.
• A finance analyst is reconciling bank statements and only has a fragment of a reference note (“INV-2023”) to locate the matching invoice.
• An operations planner needs to cross-reference parts whose descriptions contain the word “bearing”, regardless of preceding brand names or suffixes.

Traditional lookups like VLOOKUP and even the more powerful XLOOKUP expect an exact match by default. When the user only has a substring, results come back blank and productivity grinds to a halt. Workarounds such as filtering manually or using wildcards in AutoFilter slow the process and introduce human error.

That is why mastering the “text contains” variation of XLOOKUP is critical. It lets you combine the elegance of a single function with the flexibility of partial, case-insensitive searches. By embedding a wildcard pattern or pairing XLOOKUP with auxiliary functions such as SEARCH, you transform messy fragments into reliable keys. The benefits extend across industries: marketing teams mining campaign tags, HR departments locating staff by partial IDs, and logistics groups mapping shipment codes embedded in remarks.

Ignoring this skill forces teams to settle for clunky helper columns or manual searches. Worse, reports may exclude crucial rows when the lookup fails silently, leading to bad decisions. Knowing how to perform substring lookups therefore protects data quality, accelerates workflows, and connects seamlessly with other Excel techniques like dynamic arrays, FILTER, and Power Query, making you a more versatile analyst.

Best Excel Approach

The quickest, most transparent way to perform a “match text contains” lookup is to combine XLOOKUP with wildcard characters. XLOOKUP natively accepts wildcards when its sixth argument, match_mode, is set to 2 (wildcard match). By sandwiching your search fragment between a leading and trailing asterisk (*), you tell Excel: “Find any cell in the lookup array that contains this fragment anywhere inside.”

Syntax:

=XLOOKUP("*"&lookup_text&"*", lookup_array, return_array, "Not found", 2)

Parameter breakdown

• lookup_text – the substring you know (e.g., “jacket”)
• lookup_array – column you want to scan for the substring
• return_array – column from which you need the associated value
• \"Not found\" – custom message if there is no match
• 2 – wildcard match mode (supports * and ?)

Why this is best:

1. Simple: one formula, no helper columns.
2. Dynamic: automatically spills down or to the right when you wrap it in newer functions like FILTER or SORT.
3. Non-volatile: unlike older array formulas, XLOOKUP recalculates efficiently without Ctrl + Shift + Enter.

When to consider alternatives:

• You need to return multiple rows that all contain the substring (then FILTER is better).
• You must handle case-sensitive searches (combine with EXACT or FIND).
• Your organization is on Excel 2016 or older (INDEX/MATCH with SEARCH becomes the fallback).

Alternative approach returning all matches:

=FILTER(return_array, ISNUMBER(SEARCH(lookup_text, lookup_array)))

Parameters and Inputs

Before you write any formula, confirm that your source data respects these rules:

1. lookup_text (string) – can come from a cell or be typed directly in quotes. Avoid leading/trailing spaces; TRIM if uncertain.
2. lookup_array (range, same height as return_array) – usually a single column such as [B2:B500]. Data should be text; convert numbers to text if codes contain leading zeros.
3. return_array (range, parallel to lookup_array) – the field you want back, e.g., product price in [E2:E500].
4. match_mode – set to 2 for wildcard searches; leave 0 for exact matches.
5. if_not_found – optional but recommended to prevent ugly #N/A errors.
6. Multiple matches – XLOOKUP returns only the first hit. For all hits, feed the same logic into FILTER.
7. Case sensitivity – SEARCH is case-insensitive, FIND is case-sensitive. XLOOKUP’s wildcard matching is always case-insensitive.
8. Edge cases – empty lookup_text returns the first record. Guard by validating length greater than 0 with IF(LEN(lookup_text)=0,\"Enter search\",XLOOKUP(...)).

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a small product table in [A2:D11]:

• Column A: Product ID
• Column B: Product Name
• Column C: Category
• Column D: Unit Price

You want to retrieve the price when a user types any part of the product name in cell [G2].

1. Enter sample data:

2. Reserve [G1] as label “Search Term” and [G2] for input; [H1] label “Price”.

3. In [H2], enter:

=XLOOKUP("*"&G2&"*", B2:B11, D2:D11, "Not found", 2)

4. Test: type “jacket” → result 79. Type “Pack” → 120. Type “shoe” → 99.

Why it works: the asterisks allow any characters before/after the term. XLOOKUP scans [B2:B11]; as soon as it finds a cell that contains the fragment, it returns the aligned price from [D2:D11]. “Not found” prevents #N/A when the fragment is absent.

Variations

• Return the entire record by using XLOOKUP on a horizontal spill: set return_array to [A2:D11] to pull ID, name, category, price all at once.
• Validate empty input: wrap with IF(LEN(G2)=0,\"-\",XLOOKUP(...)).
• Convert to a dropdown with Data Validation for cleaner inputs, then users can still type fragments in a helper cell.

Troubleshooting

• No match? Check for leading/trailing spaces in product names; use TRIM or CLEAN.
• Partial duplication? XLOOKUP stops at the first match. If that is wrong, sort the table first or switch to FILTER.

Example 2: Real-World Application

Scenario: An HR manager needs to pull the employee email address when supplied with any part of the employee’s full name. The company roster spans 3,000 rows across departments and sits in another sheet named “Staff”.

Data on Staff sheet

• Column A: EmpID
• Column B: Full Name (Last, First Middle)
• Column C: Department
• Column D: Email

On a separate “Directory” sheet, cell [B3] is the search box. The manager wants the email in [C3] and the department in [D3].

Step-by-step

1. Ensure both sheets are in the same workbook. Use defined names if you prefer: select [Staff!B2:B3001] and name it FullNames; select [Staff!D2:D3001] and name it Emails, etc.

2. In Directory!C3 enter:

=XLOOKUP("*"&$B$3&"*", Staff!B2:B3001, Staff!D2:D3001, "No match", 2)

3. In Directory!D3 enter a parallel formula for department:

=XLOOKUP("*"&$B$3&"*", Staff!B2:B3001, Staff!C2:C3001, "", 2)

4. Try different inputs:

• “garcia” yields j.garcia@contoso.com and “Manufacturing”.
• “Chen, A” yields anna.chen@contoso.com and “Finance”.

Integration with other features

• Add a search icon shape that triggers a VBA macro or Office Script to clear prior results or log searches.
• Wrap XLOOKUP inside TEXTJOIN to concatenate multiple results if duplicates exist.
• Protect the Staff sheet and expose only the Directory sheet to non-admins.

Performance
3,000 rows is trivial for modern Excel. If you scale to 100,000 rows, ensure calculation mode is “Automatic Except Data Tables” and set lookup_array to a fixed range, not entire columns, to optimize.

Example 3: Advanced Technique

Suppose a logistics analyst must list all purchase orders whose Description contains the fragment typed in [H2], and then compute the total value. This requires more than the first match; we need every match in a spill range plus aggregation. Data lies in [A2:E50000]:

• A: PO Number
• B: Description
• C: Vendor
• D: Date
• E: Amount

Steps

1. List all matching purchase orders with FILTER:

=FILTER(A2:E50000, ISNUMBER(SEARCH($H$2, B2:B50000)), "No orders")

Explanation: SEARCH returns an array of positions (numbers) where the fragment occurs; ISNUMBER converts that to TRUE/FALSE. FILTER keeps only TRUE rows.

2. The above spills a 5-column table starting where you enter the formula. Resize columns to fit.

3. Total value of matching orders:

=SUM(FILTER(E2:E50000, ISNUMBER(SEARCH($H$2, B2:B50000))))

4. Enable dynamic summarization: use LET to compute the filter once:

=LET(
  Found, ISNUMBER(SEARCH($H$2, B2:B50000)),
  SUM(FILTER(E2:E50000, Found))
)

Edge cases handled

• Blank [H2] → FILTER returns “No orders”.
• Case sensitivity needed? Replace SEARCH with FIND.
• Performance: 50,000 rows × two formulas calculates instantly on modern hardware, but if you stack multiple SEARCHes, consider adding an index column in Power Query instead.

Professional tips

• Convert [A2:E50000] to an Excel Table named POs and reference structured names: POs[Description].
• If Excel 2019 or earlier, you cannot use FILTER. Build a helper column =ISNUMBER(SEARCH($H$2,B2)) and apply an AutoFilter, or switch to Power Query.

Tips and Best Practices

1. Anchor references: use absolute references ($B$3) for the lookup text when copying formulas horizontally.
2. Use Excel Tables: they auto-expand, keep column names, and eliminate hard-coded ranges.
3. Normalize data: strip extra spaces with TRIM/ CLEAN so wildcard matching isn’t fooled.
4. Provide meaningful if_not_found messages, e.g., \"No product found – check spelling\".
5. Optimize massive datasets by limiting lookup_array to used rows or adding a preliminary filter in Power Query.
6. Document wildcard logic in cell comments so colleagues understand the leading and trailing asterisks.

Common Mistakes to Avoid

1. Omitting the asterisks – writing "jacket" instead of "*"&"jacket"&"*" results in exact matching and blank results.
2. Leaving match_mode at default (0) – XLOOKUP then ignores wildcards. Always set 2.
3. Mismatched array sizes – lookup_array and return_array must be the same height (or width). If not, XLOOKUP throws #VALUE!.
4. Untrimmed spaces – hidden spaces in source data cause SEARCH to fail. Run TRIM across columns or use CLEAN for imported text.
5. Misleading not_found messages – if you leave it blank, #N/A shows up in reports. Supply a user-friendly string or wrap with IFERROR.

Alternative Methods

Below is a comparison of common ways to perform substring lookups:

When to choose alternatives

• Need all matches → FILTER.
• Older Excel → INDEX/MATCH+SEARCH.
• Heavy transformations or refresh from databases → Power Query.
• Strict case sensitivity across legacy systems → FIND.

Migration strategy
You can replace an INDEX/MATCH helper-column solution with XLOOKUP simply by swapping formulas once Office 365 is deployed. Keep both for a transition period and cross-check totals.

FAQ

When should I use this approach?

Use XLOOKUP with wildcards whenever you have one value to return and the search key may appear anywhere inside the cell. Perfect for quick dashboards and ad-hoc lookups.

Can this work across multiple sheets?

Yes. Point lookup_array and return_array to other sheets like Staff!B2:B500. For workbooks, include the file path if both are open; for closed workbooks, XLOOKUP still works but opens the file in the background on recalculation.

What are the limitations?

XLOOKUP stops at the first match and is not case-sensitive in wildcard mode. It also cannot search within an array created by another formula without spilling; wrap it in LET or evaluate the inner array first.

How do I handle errors?

Supply the if_not_found argument (\"No match\") or wrap the entire function in IFERROR. Validate input length to avoid querying with an empty string.

Does this work in older Excel versions?

XLOOKUP requires Microsoft 365 or Excel 2021. In Excel 2019 or earlier, combine INDEX with MATCH(1,SEARCH(...)) or adopt Power Query. FILTER also requires 365/2021.

What about performance with large datasets?

Wildcard matching is efficient, but SEARCH across 200,000 rows can slow recalculation. Limit ranges, convert to tables, or push heavy queries to Power Query / Data Model. Disable iterative calculation and volatile functions nearby.

Conclusion

Being able to “Xlookup match text contains” turns fragments of information into actionable insights with a single elegant formula. You can retrieve prices, emails, purchase orders, or any related data even when the user supplies only a partial clue. Mastering this technique not only speeds up everyday tasks but also deepens your understanding of wildcards, dynamic arrays, and error handling—skills that cascade into countless other Excel workflows. Experiment with the examples, adapt them to your datasets, and soon you will tackle fuzzy searches with confidence and precision.