# How to return columns by header name in Excel

Find each header's position with XMATCH and pick those columns with CHOOSECOLS: `=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))`.

**Excel:** `=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=CHOOSECOLS(A2:F7,MATCH(H1,A1:F1,0),MATCH(I1,A1:F1,0))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Employee | Department | Start date | Salary | Email | Phone |  | Email | Department |
| Ana Ruiz | Finance | 2021-03-15 | 68500 | ana.ruiz@corp.example | 555-0141 |  |  |  |
| Raj Patel | Sales | 2019-08-01 | 72000 | raj.patel@corp.example | 555-0172 |  |  |  |
| Lee Chen | IT | 2023-01-09 | 81000 | lee.chen@corp.example | 555-0158 |  |  |  |
| Kim Ortiz | Sales | 2020-11-23 | 69500 | kim.ortiz@corp.example | 555-0133 |  |  |  |
| Mo Diaz | Marketing | 2022-06-06 | 64000 | mo.diaz@corp.example | 555-0190 |  |  |  |
| Sam Cole | IT | 2018-04-30 | 88500 | sam.cole@corp.example | 555-0117 |  |  |  |

Formula in H2: `=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))`

Result (spills 6 × 2): ana.ruiz@corp.example, Finance; raj.patel@corp.example, Sales; lee.chen@corp.example, IT; kim.ortiz@corp.example, Sales; mo.diaz@corp.example, Marketing; sam.cole@corp.example, IT

## 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.

## Other ways

- `=INDEX(A2:F7,SEQUENCE(ROWS(A2:F7)),XMATCH(H1:I1,A1:F1))`: 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)))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/return-columns-by-header-name