Lookup & Reference
Get First & Last Text Value in a Mixed Range (Wildcard XLOOKUP)
Extract the first or last non-empty text entry from a column or row mixed with numbers and blanks using XLOOKUP("?*", range, range, "No Text Found", 2, 1) and reverse search_mode -1.
Skip numbers and empty cells to grab the earliest or latest status note!
Audit logs, project rows, and imported ledgers frequently mix numeric amounts, blank cells, and text status notes in the same range! How do you extract the FIRST text comment or the MOST RECENT text status while ignoring numbers like 100 or 450 and skipping empty cells?
Wildcard XLOOKUP solves both directions in one clean formula! Using pattern "?*" (at least one character, so empty "" strings are skipped too!) with match_mode = 2 (wildcard match) matches ONLY text entries—never numbers or booleans! Setting search_mode = 1 scans top-to-bottom for the First Text Entry, while search_mode = -1 scans bottom-to-top for the Last Text Entry!
| Row | Mixed Audit Column (A) | Matches Wildcard "?*" (match_mode = 2)? | First vs Last Text Result |
|---|---|---|---|
| A2 | 100 (Number) | No (Numbers ignored by wildcard!) | — |
| A3 | "Draft Submitted" | YES — First non-empty text! | search_mode = 1 -> "Draft Submitted" |
| A4 | 250 (Number) | No | — |
| A5 | "QA Approved" | YES — Last non-empty text! | search_mode = -1 -> "QA Approved" |
| A6 | 400 (Number) | No | — |
First Non-Empty Text Value in a Mixed Range (search_mode = 1)
Scans A2:A6 forward (search_mode = 1) with wildcard match_mode = 2 and pattern "?*" (at least 1 text character).
=XLOOKUP("?*", A2:A6, A2:A6, "No Text Found", 2, 1)Last Non-Empty Text Value in a Mixed Range (search_mode = -1)
Scans A2:A6 in reverse from bottom to top (search_mode = -1) to return the latest text entry.
=XLOOKUP("?*", A2:A6, A2:A6, "No Text Found", 2, -1)Try the example
Use the original values or change them to test your understanding.