How to Xlookup Wildcard Contains Substring in Excel

Why This Task Matters in Excel

Searching for a piece of text inside a larger text string is one of the most frequent look-up requirements in day-to-day spreadsheet work. Customer databases, product catalogs, log files, and project trackers rarely store data in perfectly standardized codes; humans type additional words, abbreviations, punctuation, and even whole sentences in the same cell. If you are tasked with locating “ACME” anywhere inside thousands of invoice descriptions, or you need to pull the correct SKU whenever the word “Eco-Friendly” appears in a product title, you quickly realise that traditional exact-match lookups fall short.

Wildcard substring lookups solve this problem by allowing you to match “containing” rather than “equals.” In operational terms this means:

• Customer Service can instantly surface all tickets that reference a recalled component even when the ticket titles vary (for example, “Battery-Issue Small,” “Battery swelled,” “Swollen battery complaint”).
• Marketing analysts can pull sales numbers for every product whose name contains “Holiday Edition” without maintaining a separate mapping table.
• Finance teams can reconcile line-item descriptions against a chart of accounts where the substring “Shipping” must be matched inside phrases like “International Shipping – FedEx.”

Microsoft Excel provides several ways to achieve this, but modern Office users overwhelmingly turn to the XLOOKUP function because it combines the flexibility of INDEX/MATCH with an intuitive, single-formula syntax. Unlike VLOOKUP, XLOOKUP can return values to the left or right of the lookup column, work natively with dynamic arrays, and provide a built-in argument for wildcard matching that is both faster and easier to read than older solutions.

Failing to master wildcard substring searches can lead to inaccurate reports, manual copy-and-paste fixes, and hours wasted scrolling through filters. On the other hand, learning the technique strengthens your general lookup skills, prepares you for more advanced dynamic-array formulas such as FILTER and SORT, and sets the foundation for automated dashboards and data-cleaning workflows.

Best Excel Approach

The most robust solution for a “contains” lookup is the XLOOKUP function with the match_mode argument set to 2 (wildcard). By concatenating the asterisk wildcard symbol (*) before and after the search substring you instruct XLOOKUP to locate the substring anywhere inside every cell of the lookup_array. When the function finds the first match, it immediately returns the corresponding item from the return_array.

Why is this approach superior?

• Simplicity – All logic lives in one readable formula instead of nested INDEX/MATCH pairs.
• Flexibility – XLOOKUP can return a single value, a spill of multiple values, or the entire matching row.
• Built-in error handling – The optional if_not_found argument prevents ugly #N/A clutter.
• Performance – XLOOKUP uses the same engine as dynamic arrays, outperforming manual helper columns and volatile functions.

The only prerequisites are (1) Microsoft 365 or Office 2021 where XLOOKUP is available and (2) a basic understanding of wildcard behaviour.

Syntax for the recommended pattern:

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

Parameter explanations:

• lookup_substring – the text fragment you are searching for.
• lookup_array – the column (or row) that may contain the fragment.
• return_array – the column (or row) that holds the value you want back.
• \"Not found\" – optional but highly recommended friendly message.
• 2 – forces wildcard matching so the asterisks (*) are treated properly.

Alternative approach for multiple hits (spill array):

=FILTER(return_array, ISNUMBER(SEARCH(lookup_substring, lookup_array)), "No match")

FILTER remains useful when you need all matches rather than the first match only.

Parameters and Inputs

Before jumping into examples, review the data requirements:

• lookup_substring
  – Data type: text.
  – May come from a typed string, a cell reference, or the result of another formula (for example, CONCAT).
  – Avoid leading/trailing spaces; use the TRIM function if needed.

• lookup_array
  – Data type: range or array containing text.
  – Must have a one-to-one correspondence with return_array (same height or width).
  – Empty cells are ignored by XLOOKUP but may cause FILTER to return blanks.

• return_array
  – Data type: range or array of any type (text, number, date).
  – Can be positioned left/right or above/below lookup_array.

• Wildcard symbol (*)
  – Represents zero or more characters.
  – Question mark (?) matches exactly one character and can be used as well.

Input validation tips:

• Convert lookup_array to an Excel Table (Ctrl+T) to make ranges dynamic.
• Remove accidental carriage returns (Alt+Enter) with CLEAN to avoid invisible mismatch.
• Ensure the workbook is saved in .xlsx or .xlsm; legacy .xls does not support XLOOKUP.

Edge cases:

• If multiple rows satisfy the condition, XLOOKUP returns the first match based on the search_mode argument (default is top-to-bottom).
• If lookup_substring is empty (\"\"), XLOOKUP treats the wildcards as “match anything” and will return the first record; use IF to block this.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a simple worksheet with a product list:

Goal: Return the price of whichever product description contains the text typed by the user in [E2].

1. In [E1] type the label “Search text”.
2. In [E2] type Bottle.
3. In [F1] type “Price result”.
4. Enter the formula in [F2]:

=XLOOKUP("*"&E2&"*", [A2:A5], [B2:B5], "Not found", 2)

Step explanations:

• \"\"&E2&\"\" tells XLOOKUP to look for any text that contains the word typed in [E2].
• [A2:A5] is the lookup_array of product names.
• [B2:B5] is the return_array (price column).
• If no description contains “Bottle,” the user sees “Not found” rather than an error.
• The argument 2 activates wildcard interpretation.

Expected result: The formula returns 12.99 because “Eco-Bottle 500ml” is the first row with the substring “Bottle.”

Variations:

• Change [E2] to “Mug” and the formula updates instantly to 19.99.
• To make the search case-insensitive (default), do nothing; XLOOKUP already ignores case.
• To display the entire product row rather than only price, wrap XLOOKUP inside CHOOSECOLS:

=CHOOSECOLS(XLOOKUP("*"&E2&"*", [A2:A5], [A2:B5], "Not found", 2), 1,2)

Troubleshooting:

• If the user types extra spaces, TRIM(E2) cleans it.
• If multiple products contain the word, only the first appears; consider FILTER if you need a list.

Example 2: Real-World Application

Scenario: A customer-support dashboard must instantly show the latest ticket ID whenever an agent types any word from the problem description. Data table in [Tickets] sheet:

Objective: When the agent types a keyword in [H2] (Dashboard sheet), return the most recent ticket that contains that keyword.

Data setup notes:

• Table name: TicketsTbl (covers [A1:D5]).
• The “Created” column is in chronological order but not necessarily sorted.

Steps:

1. Ensure “Created” is formatted as a proper date so we can sort by newest.
2. In [Dashboard!H1] type “Keyword.” In [H2] the agent writes “battery”.
3. In [Dashboard!I1] type “Latest Ticket ID.”
4. Enter the formula in [I2]:

=LET(
   kw, "*"&TRIM(H2)&"*",
   ids, TicketsTbl[Ticket ID],
   dates, TicketsTbl[Created],
   subj, TicketsTbl[Subject],
   matches, FILTER(ids, ISNUMBER(SEARCH(kw, subj))),
   matchDates, FILTER(dates, ISNUMBER(SEARCH(kw, subj))),
   indexLatest, XMATCH(MAX(matchDates), matchDates),
   INDEX(matches, indexLatest)
)

Explanation of each LET variable:

• kw: wildcard-wrapped keyword (case-insensitive).
• ids, dates, subj: separate arrays for clarity.
• matches: all ticket IDs whose Subject contains the keyword.
• matchDates: corresponding created dates.
• indexLatest: position of the maximum date within matchDates.
• Final INDEX returns the ticket ID of the newest matching record.

Why this solves the business problem: Agents no longer sift through filters. They type one word and instantly get the latest relevant case. Integrating LET makes the formula readable and more efficient because each array is calculated once.

Integration with other features:

• Conditional formatting can highlight all rows where Subject contains the keyword for visual context.
• The same keyword cell can drive a linked FILTER table that spills all matches for audit.

Performance considerations: Even with thousands of tickets, FILTER and XMATCH process quickly as they are non-volatile and use the Excel calculation engine’s vectorized operations.

Example 3: Advanced Technique

Objective: Return multiple matching columns for every record whose customer comment contains any of several possible substrings (OR logic), and spill the result into a dynamic array for reporting.

Dataset: FeedbackTbl with columns: CommentID, CommentText, Country, SentimentScore.

Search terms typed by the analyst in [M2:M4]:

Step-by-step:

1. Combine the search terms into a single pattern separated by pipes (|) to build a regular expression-like OR using the TEXTJOIN function.

=LET(
   pattern, "*"&TEXTJOIN("*|*", TRUE, M2:M4)&"*",
   matches, FILTER(FeedbackTbl[CommentText], ISNUMBER(SEARCH(pattern, FeedbackTbl[CommentText]))),
   FILTER(FeedbackTbl, ISNUMBER(SEARCH(pattern, FeedbackTbl[CommentText])))
)

But SEARCH does not understand the pipe operator. Instead we iterate:

=LET(
   terms, M2:M4,
   comments, FeedbackTbl[CommentText],
   include, BYROW(comments, LAMBDA(row, 
        OR(
            ISNUMBER(SEARCH("*"&INDEX(terms,1)&"*", row)),
            ISNUMBER(SEARCH("*"&INDEX(terms,2)&"*", row)),
            ISNUMBER(SEARCH("*"&INDEX(terms,3)&"*", row))
        )
   )),
   FILTER(FeedbackTbl, include, "No match")
)

Advanced elements introduced:

• BYROW with a LAMBDA transforms row-wise evaluation without helper columns.
• The OR function nests three SEARCH checks, but you can make it more dynamic by using REDUCE when you have an unknown number of search terms.
• The final FILTER spills all matching rows showing every column, which is perfect for dashboards or pivot-table source ranges.

Error handling and edge cases:

• If the analyst leaves all term cells blank, FILTER returns “No match.”
• SentimentScore could be blank; FILTER obviously retains blank values, but you can wrap it in IFERROR or COALESCE for clean reporting.

Professional tips:

• Convert FeedbackTbl to a PivotTable after the spill result by referencing the spilled dynamic array with the # operator (for example, [Dashboard!P2#]).
• For extremely large comment datasets (50,000+ rows), consider loading data into Power Query and applying Text.Contains for faster refresh, then pull the transformed table back into Excel and continue using XLOOKUP or FILTER for interactive slices.

Tips and Best Practices

1. Wrap lookup_substring with TRIM or CLEAN to avoid non-printing characters sabotaging the match.
2. Store lookup_array and return_array in structured tables; formulas will become self-documenting (TicketsTbl[Subject]).
3. Use LET to assign intermediate arrays when formulas exceed one line; performance and maintainability improve markedly.
4. For case-sensitive matches, replace SEARCH with FIND, or use XLOOKUP with EXACT inside FILTER.
5. When returning large spill ranges, limit visible columns with CHOOSECOLS to keep dashboards tidy.
6. Cache expensive calculations (like full SEARCH arrays) in hidden helper columns if workbook recalculation becomes sluggish.

Common Mistakes to Avoid

1. Forgetting the match_mode of 2 in XLOOKUP. Omitting it makes asterisks work only by coincidence in some builds; always set the argument explicitly.
2. Concatenating the wildcard on only one side of the substring. If you use "*"&text you will match “ends with text” but not “contains.” Always wrap both sides unless you need a starts/ends condition.
3. Mixing absolute and relative references incorrectly when copying formulas downward. Lock your lookup_substring cell with `

How to Xlookup Wildcard Contains Substring in Excel

Why This Task Matters in Excel

Searching for a piece of text inside a larger text string is one of the most frequent look-up requirements in day-to-day spreadsheet work. Customer databases, product catalogs, log files, and project trackers rarely store data in perfectly standardized codes; humans type additional words, abbreviations, punctuation, and even whole sentences in the same cell. If you are tasked with locating “ACME” anywhere inside thousands of invoice descriptions, or you need to pull the correct SKU whenever the word “Eco-Friendly” appears in a product title, you quickly realise that traditional exact-match lookups fall short.

Wildcard substring lookups solve this problem by allowing you to match “containing” rather than “equals.” In operational terms this means:

• Customer Service can instantly surface all tickets that reference a recalled component even when the ticket titles vary (for example, “Battery-Issue Small,” “Battery swelled,” “Swollen battery complaint”).
• Marketing analysts can pull sales numbers for every product whose name contains “Holiday Edition” without maintaining a separate mapping table.
• Finance teams can reconcile line-item descriptions against a chart of accounts where the substring “Shipping” must be matched inside phrases like “International Shipping – FedEx.”

Microsoft Excel provides several ways to achieve this, but modern Office users overwhelmingly turn to the XLOOKUP function because it combines the flexibility of INDEX/MATCH with an intuitive, single-formula syntax. Unlike VLOOKUP, XLOOKUP can return values to the left or right of the lookup column, work natively with dynamic arrays, and provide a built-in argument for wildcard matching that is both faster and easier to read than older solutions.

Failing to master wildcard substring searches can lead to inaccurate reports, manual copy-and-paste fixes, and hours wasted scrolling through filters. On the other hand, learning the technique strengthens your general lookup skills, prepares you for more advanced dynamic-array formulas such as FILTER and SORT, and sets the foundation for automated dashboards and data-cleaning workflows.

Best Excel Approach

The most robust solution for a “contains” lookup is the XLOOKUP function with the match_mode argument set to 2 (wildcard). By concatenating the asterisk wildcard symbol (*) before and after the search substring you instruct XLOOKUP to locate the substring anywhere inside every cell of the lookup_array. When the function finds the first match, it immediately returns the corresponding item from the return_array.

Why is this approach superior?

• Simplicity – All logic lives in one readable formula instead of nested INDEX/MATCH pairs.
• Flexibility – XLOOKUP can return a single value, a spill of multiple values, or the entire matching row.
• Built-in error handling – The optional if_not_found argument prevents ugly #N/A clutter.
• Performance – XLOOKUP uses the same engine as dynamic arrays, outperforming manual helper columns and volatile functions.

The only prerequisites are (1) Microsoft 365 or Office 2021 where XLOOKUP is available and (2) a basic understanding of wildcard behaviour.

Syntax for the recommended pattern:

CODE_BLOCK_0

Parameter explanations:

• lookup_substring – the text fragment you are searching for.
• lookup_array – the column (or row) that may contain the fragment.
• return_array – the column (or row) that holds the value you want back.
• \"Not found\" – optional but highly recommended friendly message.
• 2 – forces wildcard matching so the asterisks (*) are treated properly.

Alternative approach for multiple hits (spill array):

CODE_BLOCK_1

FILTER remains useful when you need all matches rather than the first match only.

Parameters and Inputs

Before jumping into examples, review the data requirements:

• lookup_substring
  – Data type: text.
  – May come from a typed string, a cell reference, or the result of another formula (for example, CONCAT).
  – Avoid leading/trailing spaces; use the TRIM function if needed.

• lookup_array
  – Data type: range or array containing text.
  – Must have a one-to-one correspondence with return_array (same height or width).
  – Empty cells are ignored by XLOOKUP but may cause FILTER to return blanks.

• return_array
  – Data type: range or array of any type (text, number, date).
  – Can be positioned left/right or above/below lookup_array.

• Wildcard symbol (*)
  – Represents zero or more characters.
  – Question mark (?) matches exactly one character and can be used as well.

Input validation tips:

• Convert lookup_array to an Excel Table (Ctrl+T) to make ranges dynamic.
• Remove accidental carriage returns (Alt+Enter) with CLEAN to avoid invisible mismatch.
• Ensure the workbook is saved in .xlsx or .xlsm; legacy .xls does not support XLOOKUP.

Edge cases:

• If multiple rows satisfy the condition, XLOOKUP returns the first match based on the search_mode argument (default is top-to-bottom).
• If lookup_substring is empty (\"\"), XLOOKUP treats the wildcards as “match anything” and will return the first record; use IF to block this.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a simple worksheet with a product list:

Goal: Return the price of whichever product description contains the text typed by the user in [E2].

1. In [E1] type the label “Search text”.
2. In [E2] type Bottle.
3. In [F1] type “Price result”.
4. Enter the formula in [F2]:

CODE_BLOCK_2

Step explanations:

• \"\"&E2&\"\" tells XLOOKUP to look for any text that contains the word typed in [E2].
• [A2:A5] is the lookup_array of product names.
• [B2:B5] is the return_array (price column).
• If no description contains “Bottle,” the user sees “Not found” rather than an error.
• The argument 2 activates wildcard interpretation.

Expected result: The formula returns 12.99 because “Eco-Bottle 500ml” is the first row with the substring “Bottle.”

Variations:

• Change [E2] to “Mug” and the formula updates instantly to 19.99.
• To make the search case-insensitive (default), do nothing; XLOOKUP already ignores case.
• To display the entire product row rather than only price, wrap XLOOKUP inside CHOOSECOLS:

CODE_BLOCK_3

Troubleshooting:

• If the user types extra spaces, TRIM(E2) cleans it.
• If multiple products contain the word, only the first appears; consider FILTER if you need a list.

Example 2: Real-World Application

Scenario: A customer-support dashboard must instantly show the latest ticket ID whenever an agent types any word from the problem description. Data table in [Tickets] sheet:

Objective: When the agent types a keyword in [H2] (Dashboard sheet), return the most recent ticket that contains that keyword.

Data setup notes:

• Table name: TicketsTbl (covers [A1:D5]).
• The “Created” column is in chronological order but not necessarily sorted.

Steps:

1. Ensure “Created” is formatted as a proper date so we can sort by newest.
2. In [Dashboard!H1] type “Keyword.” In [H2] the agent writes “battery”.
3. In [Dashboard!I1] type “Latest Ticket ID.”
4. Enter the formula in [I2]:

CODE_BLOCK_4

Explanation of each LET variable:

• kw: wildcard-wrapped keyword (case-insensitive).
• ids, dates, subj: separate arrays for clarity.
• matches: all ticket IDs whose Subject contains the keyword.
• matchDates: corresponding created dates.
• indexLatest: position of the maximum date within matchDates.
• Final INDEX returns the ticket ID of the newest matching record.

Why this solves the business problem: Agents no longer sift through filters. They type one word and instantly get the latest relevant case. Integrating LET makes the formula readable and more efficient because each array is calculated once.

Integration with other features:

• Conditional formatting can highlight all rows where Subject contains the keyword for visual context.
• The same keyword cell can drive a linked FILTER table that spills all matches for audit.

Performance considerations: Even with thousands of tickets, FILTER and XMATCH process quickly as they are non-volatile and use the Excel calculation engine’s vectorized operations.

Example 3: Advanced Technique

Objective: Return multiple matching columns for every record whose customer comment contains any of several possible substrings (OR logic), and spill the result into a dynamic array for reporting.

Dataset: FeedbackTbl with columns: CommentID, CommentText, Country, SentimentScore.

Search terms typed by the analyst in [M2:M4]:

Step-by-step:

1. Combine the search terms into a single pattern separated by pipes (|) to build a regular expression-like OR using the TEXTJOIN function.

CODE_BLOCK_5

But SEARCH does not understand the pipe operator. Instead we iterate:

CODE_BLOCK_6

Advanced elements introduced:

• BYROW with a LAMBDA transforms row-wise evaluation without helper columns.
• The OR function nests three SEARCH checks, but you can make it more dynamic by using REDUCE when you have an unknown number of search terms.
• The final FILTER spills all matching rows showing every column, which is perfect for dashboards or pivot-table source ranges.

Error handling and edge cases:

• If the analyst leaves all term cells blank, FILTER returns “No match.”
• SentimentScore could be blank; FILTER obviously retains blank values, but you can wrap it in IFERROR or COALESCE for clean reporting.

Professional tips:

• Convert FeedbackTbl to a PivotTable after the spill result by referencing the spilled dynamic array with the # operator (for example, [Dashboard!P2#]).
• For extremely large comment datasets (50,000+ rows), consider loading data into Power Query and applying Text.Contains for faster refresh, then pull the transformed table back into Excel and continue using XLOOKUP or FILTER for interactive slices.

Tips and Best Practices

1. Wrap lookup_substring with TRIM or CLEAN to avoid non-printing characters sabotaging the match.
2. Store lookup_array and return_array in structured tables; formulas will become self-documenting (TicketsTbl[Subject]).
3. Use LET to assign intermediate arrays when formulas exceed one line; performance and maintainability improve markedly.
4. For case-sensitive matches, replace SEARCH with FIND, or use XLOOKUP with EXACT inside FILTER.
5. When returning large spill ranges, limit visible columns with CHOOSECOLS to keep dashboards tidy.
6. Cache expensive calculations (like full SEARCH arrays) in hidden helper columns if workbook recalculation becomes sluggish.

Common Mistakes to Avoid

1. Forgetting the match_mode of 2 in XLOOKUP. Omitting it makes asterisks work only by coincidence in some builds; always set the argument explicitly.
2. Concatenating the wildcard on only one side of the substring. If you use "*"&text you will match “ends with text” but not “contains.” Always wrap both sides unless you need a starts/ends condition.
3. Mixing absolute and relative references incorrectly when copying formulas downward. Lock your lookup_substring cell with if you spill or copy across.
4. Assuming XLOOKUP scans bottom-to-top by default. It searches top-to-bottom unless you specify -1 or -2 in the search_mode argument. This matters when multiple matches exist.
5. Leaving error messages blank, resulting in #N/A cells that break chart ranges and further formulas. Provide a user-friendly “Not found” string or wrap the whole formula in IFERROR.

Alternative Methods

Although XLOOKUP with wildcards covers 95 percent of substring search needs, other methods shine in specific situations:

Decision guidance:

• Use XLOOKUP when on Microsoft 365 and you only need the first match.
• Choose FILTER if you must display every occurrence.
• Fall back to INDEX/MATCH for colleagues on older Excel versions.
• Leverage Power Query or VBA for data volumes or features beyond native formulas.

FAQ

When should I use this approach?

Employ wildcard XLOOKUP whenever you need to find a record containing a keyword inside free-form text, and you only care about the first (or latest via search_mode) match. Typical scenarios include product lookups, ticket triage, and description-based cost mapping.

Can this work across multiple sheets?

Yes. Set lookup_array and return_array to qualified sheet ranges like Inventory!A:A. XLOOKUP does not require the arrays to be on the same sheet as the formula. If your dataset spans several sheets, consider combining them with Power Query or stacking them with VSTACK.

What are the limitations?

XLOOKUP returns only one match per formula. If multiple rows contain the substring, you will need FILTER, XLOOKUP with search_mode -1 / -2 for nearest logic, or helper columns. Wildcards also treat question mark (?) as single character; escape actual question marks by doubling them (~~?).

How do I handle errors?

Use the if_not_found argument for friendly text, or wrap with IFERROR for numeric fallbacks. For example: =IFERROR(XLOOKUP(...),0) returns zero when nothing matches.

Does this work in older Excel versions?

XLOOKUP is available in Microsoft 365, Office 2021, and Excel for the web. Earlier perpetual versions (2010-2019) lack XLOOKUP; use INDEX/MATCH with wildcard or turn to Power Query.

What about performance with large datasets?

XLOOKUP leverages the dynamic array engine and is efficient up to tens of thousands of rows. For six-figure row counts you may notice recalculation delay; mitigate by converting the data to an Excel Table, disabling automatic calculation, or moving heavy searches to Power Query. FILTER and SEARCH combinations are somewhat slower than pure XLOOKUP but still acceptable under 50,000 rows.

Conclusion

Mastering wildcard substring lookups with XLOOKUP unlocks rapid, flexible data retrieval from messy text columns. By wrapping your search term in asterisks and setting match_mode to 2, you gain a one-line formula that replaces complex legacy workarounds. The technique enhances customer support dashboards, financial reconciliations, marketing analyses, and countless ad-hoc tasks. Practice with the examples in this guide, integrate LET for clarity, and explore FILTER for multi-match lists. As your confidence grows, you’ll find that substring lookups form a cornerstone skill for dynamic, modern Excel workflows—propelling you toward advanced automation, cleaner data, and faster insight generation.