Lookup & Reference
Get First or Last Match Where Cell Contains Text (Wildcard XLOOKUP)
Retrieve either the earliest or most recent transaction whose description contains a target keyword by pairing XLOOKUP match_mode = 2 (wildcards) with search_mode = 1 or -1.
Combining match_mode = 2 (Wildcard) with search_mode = 1 vs -1
Classic INDEX/MATCH("*", ...) formulas can find the first partial text match from top to bottom, but reversing them to grab the latest matching log entry requires awkward LOOKUP(2, 1/ISNUMBER(SEARCH(...))) idioms.
XLOOKUP unifies both directions cleanly: set the 5th argument match_mode = 2 to enable "*" & keyword & "*" wildcard matching, and toggle the 6th argument search_mode between 1 (first match top-down) and -1 (last match bottom-up).
| Lookup Direction | match_mode (5th arg) | search_mode (6th arg) | Use Case |
|---|---|---|---|
| First substring match | 2 (wildcard) | 1 (top-to-bottom) | Initial invoice or onboarding event |
| Last substring match | 2 (wildcard) | -1 (bottom-to-top) | Latest payment status or patch version |
| Total matching rows | COUNTIF wildcard | "*" & key & "*" | Audit how many events matched |
Forward & Reverse Wildcard XLOOKUP Formula
Wrap the search keyword in "*" & E2 & "*" and pass match_mode = 2.
=XLOOKUP("*" & E2 & "*", A2:A7, B2:B7, "NO_MATCH", 2, -1)Try the example
Use the original values or change them to test your understanding.