Lookup & Reference
Find the first literal substring with XMATCH wildcard mode
Escape wildcard characters in a search token, then find its first case-insensitive occurrence and return the matched text.
Don't forget match_mode = 2 when using wildcards inside XMATCH!
XMATCH wildcard mode 2 interprets *, ? and ~ specially; mode 0 treats them literally. MATCH uses wildcard matching with match type 0. To search for a literal substring, first escape the token and then add the surrounding * characters.
The example returns a position within A2:A6, not a worksheet row number. Position 3 refers to row 4. The first matching text wins; matching is case-insensitive. An empty search token returns no match instead of matching every text cell.
Escape ~ before * and ?. If you intentionally want the user to supply a wildcard pattern, omit the escape operation and clearly label that input as a pattern rather than a literal search token.
| Range Index | Invoice Code (A2:A6) | Contains "EU"? | XMATCH("*EU*", A2:A6, 2) |
|---|---|---|---|
| 1 (Row 2) | INV-100-US | No | Skipped |
| 2 (Row 3) | INV-205-UK | No | Skipped |
| 3 (Row 4) | INV-310-EU | Yes (First hit!) | Returns position 3! |
| 4 (Row 5) | INV-415-EU | Yes (Later hit) | Ignored (first match wins) |
| 5 (Row 6) | INV-520-JP | No | Missing "BR" -> IFNA returns 0 |
1-based position of first partial match using XMATCH (match_mode = 2)
The escaped token is surrounded by wildcards to match anywhere within the text. Missing matches return zero.
=IF(COUNTBLANK(D2)>0,0,IFNA(XMATCH("*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?") & "*", A2:A6, 2), 0))Retrieve the full text of the first partial-matching cell
INDEX uses the relative position from the same lookup range. Missing or empty search text returns None. IFNA handles missing matches, not unrelated data errors.
=LET(matchPosition,IF(COUNTBLANK(D2)>0,0,IFNA(XMATCH("*" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?") & "*", A2:A6, 2), 0)),IF(matchPosition=0,"None",INDEX(A2:A6,matchPosition)))Try the example
Use the original values or change them to test your understanding.