How to Partial Match With Numbers And Wildcard in Excel

Why This Task Matters in Excel

In almost every data-driven department—sales, logistics, finance, customer service—you eventually face a list that mixes text and numbers in the same cell. Product SKUs like “T-XL-125-BLK”, invoice references such as “INV-2023-00457”, or shipment IDs like “SHIP_01982_US” are all classic examples. Business processes often require locating or aggregating information by only part of those codes:

• A customer only remembers the last three digits of an invoice number and asks for its status.
• Procurement wants a list of all items that contain the gauge “14” regardless of the other characters.
• Accounting needs to sum every expense line whose reference ends with “-99”.

Because the text and numeric portions form a single string, traditional exact matching fails. Partial matching with wildcards lets you interrogate the data without manually separating text and number components. This capability accelerates tasks such as filtering large order tables, reconciling transactions, or generating targeted reports.

Excel is particularly well suited for this job. Built-in wildcard awareness in functions like XLOOKUP, COUNTIF, SUMIFS, and FILTER, combined with powerful text functions such as SEARCH and FIND, lets you craft formulas that pick out any piece of a code—at the beginning, the middle, or the end. Once you master the technique, you can hand the same workbook to another colleague: no VBA, no SQL, no add-ins required.

Failing to understand partial matching can have real consequences. Help-desk teams might send the wrong replacement part, auditors may overlook misfiled invoices, and analysts could double-count sales. Mastery of wildcard-based lookup ties directly into broader Excel workflows like dynamic reports, dashboards, and data validation—making you faster, more accurate, and easier to collaborate with.

Best Excel Approach

The best overall method today is XLOOKUP with its wildcard match mode. It supports dynamic arrays, requires only one function, and automatically spills multiple results when combined with the FILTER wrapper. XLOOKUP’s [match_mode] parameter set to 2 tells Excel to treat the lookup value as containing wildcards.

Syntax (core lookup):

=XLOOKUP("*"&lookup_number&"*", lookup_range, return_range, "Not found", 2)

Explanation

• lookup_number – the numeric string you want to locate; it can be typed, referenced, or calculated.
• \"\"&lookup_number&\"\" – concatenates an asterisk before and after, allowing unknown characters on either side.
• lookup_range – single-column range containing mixed text and numbers (for example [B2:B100]).
• return_range – parallel range from which you want the result (for example [C2:C100]).
• \"Not found\" – optional message when nothing matches.
• 2 – tells XLOOKUP to use wildcard matching.

When to adopt this approach
Use it whenever you are on Microsoft 365 or Excel 2021+, need a single returned value, and expect regular string patterns. For multiple hits, wrap it in FILTER:

=FILTER(return_range, ISNUMBER(SEARCH(lookup_number, lookup_range)))

Legacy alternatives, covered later, include INDEX/MATCH with wildcards or COUNTIF-based tests. They remain helpful for older Excel versions or where you cannot enable dynamic arrays.

Parameters and Inputs

• Lookup value (required): can be numeric, alphanumeric, or solely text. If the source value is numeric but stored as a number, convert it to text with TEXT or the ampersand (&\"\").
• Lookup range (required): a single column containing mixed strings. Ensure there are no leading or trailing spaces—use TRIM to clean if needed.
• Return range (optional): any parallel column. If you only want a TRUE/FALSE result, omit the return range and use ISNUMBER(SEARCH()) instead.
• Wildcard placement: an asterisk (*) represents any string—including nothing—while a question mark (?) represents exactly one character. Place wildcards before, after, or around the lookup value depending on the pattern needed.
• Data validation: confirm that both lookup_range and return_range are the same height, otherwise XLOOKUP and INDEX will return #VALUE errors.
• Edge cases: Watch for case sensitivity. SEARCH, COUNTIF, XLOOKUP wildcard mode, and SUMIFS are case-insensitive; FIND is case-sensitive. For binary parts such as “AB123” versus “ab123” decide if you need exact case.

Step-by-Step Examples

Example 1: Basic Scenario – Find a Product by Partial SKU

Suppose you have the following in Sheet 1:

A customer only supplies “125” as the part of the SKU.

1. In cell E2 enter the partial code 125.
2. In F2 enter:

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

Result: “T-Shirt Black XL”.

Why it works

• The lookup value becomes “125”, so any SKU containing “125” qualifies.
• XLOOKUP scans [A2:A4], finds the first string that fulfills the wildcard pattern, and returns the corresponding Name.

Variations

• To retrieve Price instead, change return_range to [C2:C4].
• To allow input like “-200” (dash-number) but still match, instruct users to skip the dash and rely on the surrounding asterisks.

Troubleshooting

• Blank result? Confirm that E2 is text. If someone types 125 and column A has “125” within text, XLOOKUP still works. But if they enter leading zeros (e.g., 0125) convert with VALUE or TEXT to ensure comparability.
• Multiple matches? Only the first is returned. Use Example 2’s FILTER approach if you need all.

Example 2: Real-World Application – Return All Invoices Containing a Partial Number

Scenario
Accounts Receivable maintains 5 000 rows of invoices in Sheet Invoices. Column A holds invoice numbers like “INV-2023-00457”. The auditor wants every invoice containing “004”.

Data layout:

Step-by-Step

1. In sheet Audit, cell B1 label “Partial #”. In B2 type 004.
2. In B4 enter:

=FILTER(Invoices!A:D, ISNUMBER(SEARCH(B2, Invoices!A:A)), "No matches")

3. Press Enter. Excel spills the entire table of matching rows into columns B:E.

Explanation of each function

• SEARCH(B2, Invoices!A:A) returns an array of positions where “004” appears; unmatched rows produce #VALUE.
• ISNUMBER turns the positions into TRUE/FALSE.
• FILTER keeps rows marked TRUE.

Business benefit
You export a ready-made audit list without sorting, subtotals, or manual copy/paste. Any time you change B2, the list refreshes—perfect for investigative work.

Performance considerations

• SEARCH over 5 000 rows is close to instantaneous on modern hardware, but for hundreds of thousands of rows consider indexing invoices in Power Query then load only the filtered result.
• If you merely need totals, swap FILTER for SUMIFS (see Example 3).

Example 3: Advanced Technique – Sum Values for Codes Ending in a Specific Number

Operations wants to know total volume for all part numbers ending with “-99” across 40 000 rows. Dynamic arrays are mainstream, but many plants still use Excel 2016. You can combine SUMIFS with a trailing wildcard pattern.

Data in Sheet Parts:

Formula in D2:

=SUMIFS([B2:B40001], [A2:A40001], "*-99")

Logic

• Criteria \"*-99\" means any string that finishes with “-99”.
• SUMIFS adds the Quantity column for all such rows.

Edge cases

• If part numbers might have trailing spaces, wrap the criteria range in TRIM within SUMPRODUCT:

=SUMPRODUCT((TRIM([A2:A40001])="*-99")*[B2:B40001])

Professional tips

• In Excel 365 you can simplify with LET and dynamic arrays.
• For multiple criteria (for example ending “-99” and starting “AA”) use SUMIFS with two separate criteria: \"AA*\" and \"*-99\".

Tips and Best Practices

1. Always concatenate wildcards explicitly. \"\"&value&\"\" is more readable than placing the asterisk inside the cell.
2. Normalize data first: TRIM to remove spaces and UPPER/LOWER if you want case-consistent comparisons.
3. Convert pure numbers to text with &\"\" to avoid false mismatches inside SEARCH.
4. Use FILTER plus CHOOSECOLS to return only necessary columns, keeping your output concise.
5. Document your intent: name ranges like rngSKU or tblInvoice[Invoice No] so formulas self-explain.
6. Break complex formulas with LET; easier debugging, faster calculation on large sheets.

Common Mistakes to Avoid

• Forgetting wildcard mode in XLOOKUP – Omitting the 2 in [match_mode] triggers exact match and returns #N/A. Add the parameter or wrap in asterisks and use SEARCH instead.
• Mismatched range sizes – INDEX/MATCH and SUMIFS will error if lookup_range and return_range have different lengths. Always make them symmetrical.
• Case-sensitive confusion – Using FIND when you meant SEARCH yields empty results if cases differ. Decide on your sensitivity and pick accordingly.
• Over-filtering – \"1\" also catches “10”, “21”, “31”. If you require numbers separated by delimiters, refine with \"-1-\" or REGEX functions (365 only).
• Hard-wiring wildcards in the lookup cell – Users might mistakenly include their own asterisk, doubling it in the formula. Keep asterisks inside the formula for consistency.

Alternative Methods

When to pick each

• Need backward compatibility and only the first hit? Go with INDEX/MATCH.
• Want counts or totals? COUNTIF(S) or SUMIFS.
• Massive datasets? Load into Power Query, filter by “Contains”.
• Need complex rules such as “starts with A, ends with 99, and has exactly 2 digits in the middle”? Use REGEX functions in 365.

FAQ

When should I use this approach?

Use wildcard partial matching when your identifier is embedded inside larger text strings and you cannot rely on fixed positions. It is ideal for ad-hoc lookups, reconciliation tasks, and dynamic reporting dashboards.

Can this work across multiple sheets?

Yes. Prefix your ranges with the sheet name, e.g., XLOOKUP(\"\"&G2&\"\", Sheet2![A:A], Sheet2![B:B], \"No hit\", 2). For three or more sheets use CHOOSE or INDIRECT to bundle the ranges, or move data into a single table and add a “Source Sheet” column for easier maintenance.

What are the limitations?

Wildcards cannot replace an empty string in the middle when using the question mark. SEARCH is limited to 255-character patterns. XLOOKUP returns only the first match unless wrapped in FILTER. Older Excel lacks dynamic arrays, so retrieving multiple rows requires array formulas or helper columns.

How do I handle errors?

Wrap formulas inside IFERROR or supply the not_found argument in XLOOKUP. To debug, test SEARCH separately—if it returns #VALUE, the substring is missing. Use LEN to diagnose unexpected spaces.

Does this work in older Excel versions?

Yes, but with different functions. INDEX/MATCH and COUNTIF/SUMIFS with \"\"&value&\"\" have existed since Excel 2007. Dynamic spilling (FILTER, XLOOKUP) is unavailable prior to 365/2021, so you will need Ctrl+Shift+Enter array formulas for multi-row output.

What about performance with large datasets?

Wildcards scan each string character-by-character. On 100 000+ rows they remain acceptable, but SUMIFS is generally faster than array-based SEARCH. For millions of rows move data to Power Query or Power Pivot, then filter on the model side.

Conclusion

Partial matching with numbers and wildcards unlocks a versatile set of lookup and aggregation tricks that save hours of manual scrolling. Whether you adopt XLOOKUP for its simplicity or fall back on INDEX/MATCH and SUMIFS for compatibility, the core principle—wrapping your search term with asterisks—remains the same. Master this skill and you will navigate messy real-world codes, generate dynamic reports, and respond to stakeholder requests with confidence. Next, experiment with combining wildcard logic and dynamic arrays to build interactive dashboards that refresh at the speed of thought.