Lookup & Reference
Rearrange and select table columns dynamically by header name (CHOOSECOLS + XMATCH)
Reshape messy CSV exports into your exact target column order—while dropping unwanted columns—by matching header names with XMATCH inside CHOOSECOLS.
Stop manually cutting and pasting columns every time a vendor CSV changes order!
External HRIS and CRM exports frequently scramble column order ("EmpID", "Salary", "Dept", "FullName") or insert new columns mid-table, breaking hardcoded VLOOKUP column numbers.
XMATCH locates the requested headers and CHOOSECOLS returns the columns in the requested order. The source ranges remain fixed at A1:D1 and A2:D4; extend both when adding columns or rows outside them.
| Raw CSV Order (A1:D1) | XMATCH Position | Target Clean View Order |
|---|---|---|
| Col 4: "FullName" | XMATCH("FullName", A1:D1) = 4 | Output Col 1: FullName |
| Col 3: "Dept" | XMATCH("Dept", A1:D1) = 3 | Output Col 2: Dept |
| Col 1: "EmpID" | XMATCH("EmpID", A1:D1) = 1 | Output Col 3: EmpID (Col 2 Salary omitted!) |
Reorder and subset columns by exact header label
Extracts FullName, Dept, and EmpID in that exact sequence regardless of where they sit in A1:D1.
Required headers must each occur exactly once. Omitting Salary from this output is not access control: the source salary column remains in the workbook and can still be accessed by users with workbook access.
=CHOOSECOLS(A2:D4,XMATCH("FullName",A1:D1),XMATCH("Dept",A1:D1),XMATCH("EmpID",A1:D1))Look up a single column dynamically by header name with XLOOKUP
XLOOKUP on headers A1:D1 returning A2:D4 spills the entire requested column vector.
A missing header produces a lookup error and duplicate headers select the first match. Check header uniqueness before reshaping an export; clear the output area for the full spill.
=XLOOKUP(F2,A1:D1,A2:D4)Try the example
Use the original values or change them to test your understanding.