How to return columns by header name in Excel
Excel formula
=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))Find each header's position with XMATCH and pick those columns with CHOOSECOLS: =CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1)).
- Result on the example
- a 6-row list that spills from H2 in
H2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.
How it works
XMATCH looks up both headers in H1:I1 at once and returns their positions in the header row: 5 for Email and 2 for Department. CHOOSECOLS then returns those columns of the data, in the order of the headers you typed, so the result lines up under them.
If someone moves or inserts a column in the source table, the formula still finds it by name. In Google Sheets, give MATCH each header on its own.
=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))Result{6 rows × 2 cols} "ana.ruiz@corp.example", "Finance", "raj.patel@corp.example", "Sales", …
A2:F76 rows × 6 cols: "Ana Ruiz", "Finance", 3/15/2021, $68,500, …A range: 6 rows × 6 columns.
H1:I11 row × 2 cols: "Email", "Department"A range: 2 cells in one row.
A1:F11 row × 6 cols: "Employee", "Department", "Start date", "Salary", …A range: 6 cells in one row.
XMATCH(H1:I1,A1:F1){1 row × 2 cols} 5, 2XMATCH: Modern MATCH: exact by default.- lookup_value
H1:I11 row × 2 cols: "Email", "Department" - lookup_array
A1:F11 row × 6 cols: "Employee", "Department", "Start date", "Salary", …
- lookup_value
CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1)){6 rows × 2 cols} "ana.ruiz@corp.example", "Finance", "raj.patel@corp.example", "Sales", …CHOOSECOLS: The columns you name from an array, in the order you name them.- array
A2:F76 rows × 6 cols: "Ana Ruiz", "Finance", 3/15/2021, $68,500, … - col_num1
XMATCH(H1:I1,A1:F1){1 row × 2 cols} 5, 2
- array
In Google Sheets
Google Sheets needs a different formula: =CHOOSECOLS(A2:F7,MATCH(H1,A1:F1,0),MATCH(I1,A1:F1,0)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:F7,SEQUENCE(ROWS(A2:F7)),XMATCH(H1:I1,A1:F1)) | spills 6 × 2 | Works in Excel 2021, which has no CHOOSECOLS: INDEX takes every row number and the two column numbers. |
| =FILTER(A2:F7,ISNUMBER(MATCH(A1:F1,H1:I1,0))) | spills 6 × 2Different result | FILTER can keep columns too, when the condition is a row. The columns stay in the table's order. Department comes before Email, as in the source table. |
Common mistakes
- A header that isn't found makes XMATCH return #N/A and the whole result fails. Check spelling and stray spaces in the header cells.
- XMATCH needs the lookup row to be the header row only,
A1:F1, not the whole table. - CHOOSECOLS needs Excel 2024, Microsoft 365 or Google Sheets. In Excel 2021, use the INDEX version or one XLOOKUP per column.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return columns by header name
H2, return the columns of A2:F7 whose headers are typed in H1:I1, in that order.Questions
How do I return one column by its header?
Use XLOOKUP on the header row: =XLOOKUP("Email",A1:F1,A2:F7) returns the whole Email column.
Can I filter the rows at the same time?
Wrap the table in FILTER first: =CHOOSECOLS(FILTER(A2:F7,B2:B7="Sales"),XMATCH(H1:I1,A1:F1)).
How do I include the headers in the result?
Start both ranges at row 1: =CHOOSECOLS(A1:F7,XMATCH(H1:I1,A1:F1)), and put the result somewhere other than below H1:I1.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.