Lookup & Reference
Two-way XLOOKUP: dynamic row and column matrix lookup
Nest one XLOOKUP inside another to retrieve the intersection of a row label and a column header without hardcoded column numbers.
How inner XLOOKUP returns an entire column vector
Unlike VLOOKUP, XLOOKUP can return a multi-row slice when return_array is a 2D table. Looking up "Q2" across headers B1:D1 with return_array B2:D4 returns the vertical Q2 column [145; 130; 125].
The outer XLOOKUP then searches Region A2:A4 for "South" and picks the matching element from that dynamically selected column (130).
| Region (A) | Q1 (B) | Q2 (C) | Q3 (D) |
|---|---|---|---|
| North | 120 | 145 | 160 |
| South | 95 | 130 | 150 |
| West | 110 | 125 | 140 |
Nested XLOOKUP formula
With PickRegion = "South" in E2 and PickQuarter = "Q2" in F2, the nested lookup intersects row 2 of the table with column 2 of B2:D4 to return 130.
=XLOOKUP(E2, A2:A4, XLOOKUP(F2, B1:D1, B2:D4))Guard both missing column headers and missing row labels
Check the header with scalar XMATCH first. If it exists, run the nested lookup with a separate missing-region message. Testing ISNA on the entire returned column can create an array of flags rather than one scalar decision.
=IF(ISNA(XMATCH(F2, B1:D1, 0)), "No quarter", XLOOKUP(E2, A2:A4, XLOOKUP(F2, B1:D1, B2:D4), "No region"))Try the example
Use the original values or change them to test your understanding.