Lookup & Reference
Find the last matching revision and release tag with reverse XLOOKUP
Search an append-ordered revision log from bottom to top using a literal filename prefix and return the matching filename or release tag.
Combine wildcard prefix matching with bottom-to-top search in one formula!
Reverse search returns the bottommost matching row. It means latest only when the log is ordered appropriately; it does not compare semantic version numbers, timestamps, release status or revision dates. An older revision appended last will be returned.
The sample release tags are text labels, not spreadsheet dates. Use a separate date-based selection when timestamps, rather than source order, define recency. A matching draft is not automatically excluded.
Treat D2 as a literal nonempty text prefix. Escape tilde, asterisk and question mark before adding the final wildcard *. Include a delimiter such as the trailing hyphen to avoid matching a different document family with the same initial letters. Matching is case-insensitive and whitespace is preserved.
| Row | Document Revision (Col A) | Release Tag (Col B) | Reverse Wildcard Match for "SOP-Auth-*" |
|---|---|---|---|
| 2 | SOP-Auth-v1.0 | 2026-Q1-Draft | Skipped (obsolete draft) |
| 3 | CAD-Pump-v1.0 | 2026-Q1-Init | Different document stem |
| 4 | SOP-Auth-v1.1 | 2026-Q1-RevA | Skipped (superseded) |
| 5 | CAD-Pump-v2.0 | 2026-Q1-Prod | Latest for "CAD-Pump-" |
| 6 | SOP-Auth-v2.0 | 2026-Q1-Final | First hit from bottom -> Returned! |
Return the last matching filename
Escape ~ first, then * and ?, and append one unescaped * to match the remaining suffix. Search mode -1 chooses the last source row.
=IF(OR(NOT(ISTEXT(D2)),COUNTBLANK(D2)>0),"Enter prefix",XLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*", A2:A6, A2:A6, "No Revision Found", 2, -1))Return the associated release tag
Use exactly the same match and search modes to return column B from the chosen row. This is a tag lookup, not date arithmetic.
=IF(OR(NOT(ISTEXT(D2)),COUNTBLANK(D2)>0),"Enter prefix",XLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*", A2:A6, B2:B6, "No Revision Found", 2, -1))Try the example
Use the original values or change them to test your understanding.