Lookup & Reference
Partial Substring Match on Numeric Cells (Why "*45*" Fails & SEARCH Fix)
Match, count, sum, and look up numeric invoice IDs or part numbers by embedded digit pattern without converting numeric columns to text, overcoming COUNTIF and XLOOKUP wildcard failure on numbers.
Why COUNTIF(A2:A6, "*45*") and wildcard XLOOKUP miss genuine numbers
Spreadsheet wildcard operators (* and ?) only inspect text strings. When invoice numbers or GL account codes are stored as true IEEE-754 numbers (e.g., 10452, 24500, 88451), COUNTIF(A2:A6, "*45*") and XLOOKUP("*45*", A2:A6, B2:B6, "NONE", 2) silently skip every numeric cell and only match text-formatted entries.
Coercing the lookup array into text inside memory via "" & A2:A6 (guarded by NOT(ISBLANK(A2:A6)) so blank cells never turn into "0" when searching for digit "0") lets ISNUMBER(SEARCH(pattern, "" & A2:A6)) or XLOOKUP("*" & pattern & "*", "" & A2:A6, ..., "NONE", 2) find embedded digits while keeping the worksheet column purely numeric.
| Pattern Goal | Broken Wildcard Attempt | Type-Safe Numeric + Text Formula |
|---|---|---|
| Count IDs containing 45 | COUNTIF(A2:A6, "*45*") -> misses numbers | SUMPRODUCT(--NOT(ISBLANK(A2:A6)), --ISNUMBER(SEARCH(E2, "" & A2:A6))) |
| Sum amounts for IDs containing 45 | SUMIF(A2:A6, "*45*", C2:C6) -> 0 | SUMPRODUCT(--NOT(ISBLANK(A2:A6)), --ISNUMBER(SEARCH(E2, "" & A2:A6)), C2:C6) |
| First matching description | XLOOKUP("*45*", A2:A6, B2:B6, , 2) | INDEX(B2:B6, MATCH(TRUE, ISNUMBER(SEARCH(E2, IF(ISBLANK(A2:A6), "", "" & A2:A6))), 0)) |
Count & Sum Numeric or Mixed Codes Containing Digit Substring
Project raw numeric/mixed IDs into a blank-guarded text helper column D2:D6 (=IF(ISBLANK(A2), "", "" & A2)) so COUNTIF, SUMIF, and wildcard XLOOKUP match embedded digits while leaving Column A numeric.
=COUNTIF(D2:D6, "*" & F2 & "*")First Partial-Match Lookup Across Numeric IDs
Run wildcard XLOOKUP (match_mode = 2) against the blank-guarded text key D2:D6 to return the first matching component.
=XLOOKUP("*" & F2 & "*", D2:D6, B2:B6, "NO_MATCH", 2)Try the example
Use the original values or change them to test your understanding.