Dynamic Arrays
Combine & Reorder Virtual Columns with CHOOSECOLS & HSTACK (Left VLOOKUP)
Assemble custom in-memory lookup tables in any column order—including right-to-left VLOOKUP without physically rearranging worksheet columns—using CHOOSECOLS and HSTACK.
Build virtual two-column tables in any order without moving sheet columns!
CHOOSECOLS(A2:C5,3,1) constructs an array with ID then name; HSTACK(C2:C5,B2:B5) constructs ID then region. VLOOKUP can search the first column of either temporary array and return its second column, even though the source value was originally left of the ID.
HSTACK joins by row position, not by matching identifiers. The ranges in this example have equal heights and represent the same records in the same order. Combining independently sorted tables would silently associate unrelated records.
The source columns stay in place. A standalone array formula spills into cells, which must have free space. Duplicate lookup keys return the first matching row. IFNA supplies a missing-ID message; it can also replace an actual #N/A in a matched return cell, so inspect source errors when troubleshooting.
| RepName (Col A) | Region (Col B) | EmpID (Col C) | Virtual CHOOSECOLS(A2:C5, 3, 1) |
|---|---|---|---|
| Maya | West | E-401 | {"E-401", "Maya"} |
| Liam | East | E-402 | {"E-402", "Liam"} |
| Noah | North | E-403 | {"E-403", "Noah"} -> VLOOKUP match! |
| Zoe | South | E-404 | {"E-404", "Zoe"} |
Right-to-Left VLOOKUP via Virtual CHOOSECOLS Reordering
Selects column 3 (EmpID) as virtual column 1 and column 1 (RepName) as virtual column 2.
=IF(COUNTBLANK(E2)>0,"Enter ID",IFNA(VLOOKUP(E2, CHOOSECOLS(A2:C5, 3, 1), 2, FALSE),"Unknown ID"))Combining Non-Adjacent Ranges Side-by-Side with HSTACK
These equal-height ID and region ranges are aligned record by record. This is horizontal array assembly, not a relational join. Modern Excel function availability depends on version; verify in the target version.
=IF(COUNTBLANK(E2)>0,"Enter ID",IFNA(VLOOKUP(E2, HSTACK(C2:C5, B2:B5), 2, FALSE), "Unknown ID"))Inspect the reordered ID/name array
Enter this standalone formula in an empty output area to see the four rows and two columns used by the lookup.
=CHOOSECOLS(A2:C5,3,1)Inspect the ID/region array
The first result row combines C2 with B2, the second C3 with B3, and so on.
=HSTACK(C2:C5,B2:B5)Try the example
Use the original values or change them to test your understanding.