Lookup & Reference
Horizontal XLOOKUP Across Columns: Upward Return & Right-to-Left Last Match
Replace fragile row-index HLOOKUP formulas with horizontal XLOOKUP across column headers, including upward lookups into rows above the match row and right-to-left latest-revision searches.
Why horizontal XLOOKUP outperforms HLOOKUP on wide financial schedules
Classic HLOOKUP(lookup_value, table_array, row_index_num, FALSE) suffers from three structural limitations on horizontal forecast sheets: it defaults to approximate matching when the 4th argument is omitted, breaks whenever a row is inserted inside table_array, and cannot return a row located above the lookup row.
Because XLOOKUP separates lookup_array (e.g., B2:F2) from return_array (e.g., B1:F1 above or B4:F4 below), horizontal lookups remain immune to inserted rows, default to exact match automatically, and support search_mode = -1 to retrieve the rightmost column when revised forecast columns share a period tag.
| Horizontal Lookup Task | HLOOKUP Capability | XLOOKUP Formula |
|---|---|---|
| Exact match downward | Requires hardcoded row_index_num | XLOOKUP(B6, B2:F2, B4:F4, "MISSING") |
| Upward lookup (row above key) | Impossible (#VALUE! if row < 1) | XLOOKUP(B7, B4:F4, B1:F1, "NO_MATCH") |
| Rightmost duplicate period | Returns leftmost match only | XLOOKUP(B6, B2:F2, B4:F4, "MISSING", 0, -1) |
Exact Horizontal Lookup & Rightmost Revision Formula
Pass 1xN horizontal ranges for both lookup_array and return_array; set the 6th argument (search_mode) to -1 when revised columns are appended on the right.
=XLOOKUP(B6, B2:F2, B4:F4, "MISSING", 0, -1)Upward Horizontal Lookup Into Header Row Above
Search across a bottom metric row (such as Peak Revenue in B4:F4) and return the corresponding Owner or Quarter header in B1:F1 above it.
=XLOOKUP(MAX(B4:F4), B4:F4, B1:F1, "NONE")Try the example
Use the original values or change them to test your understanding.