Lookup & Reference
Lookup Latest File Version, Artifact Filename & Author (XLOOKUP Reverse Search)
Retrieve the most recently appended file revision, version integer, and uploader for any document family in a chronological release log using XLOOKUP search_mode = -1 and MAXIFS.
Chronological append logs vs out-of-order version integers
Engineering and finance document registries append a new row every time a specification or model workbook is revised (v1, v2, v3, ...). Standard top-down VLOOKUP or XLOOKUP always stops at the oldest v1 row at the top of the log.
Setting XLOOKUP’s 6th argument (search_mode) to -1 scans bottom-to-top and returns the latest appended filename or reviewer immediately. Pairing that with MAXIFS(Version_Col, Doc_Col, Target_Doc) cross-verifies the highest numeric revision even if historical imports were pasted out of chronological order.
| Query Goal | Formula Pattern | Result for "Spec_API" |
|---|---|---|
| Latest Appended Filename | XLOOKUP(F2, A2:A6, C2:C6, "UNPUBLISHED", 0, -1) | Spec_API_v3_final.xlsx |
| Latest Revision Author | XLOOKUP(F2, A2:A6, D2:D6, "UNPUBLISHED", 0, -1) | Elena |
| Highest Version Integer | MAXIFS(B2:B6, A2:A6, F2) | 3 |
Bottom-Up Latest Filename Lookup Formula
Pass match_mode = 0 (exact match) and search_mode = -1 (last-to-first) to XLOOKUP so newly appended rows at the bottom win automatically.
=XLOOKUP(F2, $A$2:$A$6, $C$2:$C$6, "UNPUBLISHED", 0, -1)Highest Version Number Verification with MAXIFS
Retrieve the maximum numeric version tag for the same document family to audit monotonic v1 -> v2 -> v3 numbering.
=MAXIFS($B$2:$B$6, $A$2:$A$6, F2)Try the example
Use the original values or change them to test your understanding.