Lookup & Reference
Partial-Match Wildcard Lookup: VLOOKUP("*" & Key & "*") & XLOOKUP Mode 2
Look up vendor records or catalog SKUs by partial embedded code using VLOOKUP("*" & E2 & "*", A2:C5, 3, FALSE) or XLOOKUP("*" & E2 & "*", ..., 2).
Why XLOOKUP ignores "*" wildcards by default unless you pass match_mode = 2!
Exact-match VLOOKUP("*" & E2 & "*", A2:C5, 3, FALSE) automatically treats "*" as any zero-or-more-character sequence and "?" as any single character! By contrast, XLOOKUP defaults to exact LITERAL matching (match_mode = 0)—so XLOOKUP("*" & E2 & "*", A2:A5, C2:C5) searches for literal asterisk characters and fails with #N/A unless you explicitly pass 5th argument match_mode = 2!
Always guard empty search input E2 = "" first—because "*" & "" & "*" simplifies to "**", which silently matches the VERY FIRST non-empty text row in A2:A5 instead of prompting for a search code!
| Partial Substring Query E2 | Wildcard Pattern "*" & E2 & "*" | Matched Catalog Row & Price | Blank-Query Protection |
|---|---|---|---|
| E2 = "4090" (GPU Part Code) | "*4090*" | NV-RTX-4090-OC -> $1,799 | Matches embedded middle token |
| E2 = "7950X" (CPU Part Code) | "*7950X*" | AMD-RYZ-7950X-BOX -> $549 | Identical in VLOOKUP & XLOOKUP(..., 2) |
| E2 = "" (Blank Search Box!) | Blocked Before "**" Match! | "Enter Search Substring" | Prevents false Row-1 match |
Blank-Safe Partial-Match VLOOKUP("*" & E2 & "*", ..., FALSE)
Rejects blank or whitespace-only search input via OR(ISBLANK(E2), LEN(TRIM(E2 & "")) = 0) and otherwise performs exact-mode wildcard substring VLOOKUP returning column 3.
=IF(OR(ISBLANK(E2), LEN(TRIM(E2 & "")) = 0), "Enter Search Substring", IFNA(VLOOKUP("*" & E2 & "*", A2:C5, 3, FALSE), "No Partial SKU Match"))Equivalent XLOOKUP Wildcard Substring Formula (match_mode = 2)
Enables wildcard character matching via XLOOKUP’s 5th argument (2) with built-in 4th argument fallback.
=IF(OR(ISBLANK(E2), LEN(TRIM(E2 & "")) = 0), "Enter Search Substring", XLOOKUP("*" & E2 & "*", A2:A5, C2:C5, "No Partial SKU Match", 2))Try the example
Use the original values or change them to test your understanding.