# How to make the VLOOKUP column number dynamic in Excel

Let MATCH find the column number from the header: `=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)`.

**Excel:** `=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Employee ID | Name | Department | Title | Start date | Salary |  | Employee ID | E-1046 |
| E-1041 | Marcus Lee | Finance | Analyst | 2021-03-15 | 68500 |  | Field | Salary |
| E-1042 | Priya Nair | Marketing | Manager | 2019-08-01 | 92400 |  | Value |  |
| E-1043 | Tom Becker | Sales | Account exec | 2022-01-10 | 59800 |  |  |  |
| E-1044 | Rosa Diaz | IT | Engineer | 2020-06-22 | 81250 |  |  |  |
| E-1045 | Ken Watanabe | Sales | Team lead | 2018-11-05 | 76900 |  |  |  |
| E-1046 | Aisha Khan | Finance | Controller | 2017-04-17 | 98100 |  |  |  |
| E-1047 | Leo Novak | IT | Support | 2023-02-27 | 51950 |  |  |  |

Formula in I3: `=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)`

Result: $98,100

## How it works

MATCH looks up the field name in the header row `A1:F1` and returns its position, 6 for Salary. VLOOKUP uses that as its column number, so changing `I2` to another header returns that field, and inserting a column no longer breaks the formula.

## Other ways

- `=INDEX(A2:F8,MATCH(I1,A2:A8,0),MATCH(I2,A1:F1,0))`: The INDEX version of the same idea, which also works when the ID column isn't first.
- `=XLOOKUP(I1,A2:A8,XLOOKUP(I2,A1:F1,A2:F8))`: Nested XLOOKUP: the inner one picks the column by header, the outer one picks the row. Excel 2021, Microsoft 365 and Google Sheets.

## Common mistakes

- The header range must start in the same column as the table. `B1:F1` with `A2:F8` returns the column one to the left of the one you meant.
- Headers must match exactly, including spaces. "Start date " with a trailing space returns #N/A.
- A date field comes back as a number unless the result cell has a date format.

## Questions

**How do I fill this across to return several fields?**

Put the field names in a header row and lock the ID: `=VLOOKUP($I$1,$A$2:$F$8,MATCH(J$1,$A$1:$F$1,0),FALSE)`.

**Why does my VLOOKUP return the wrong column after inserting a column?**

A typed column number doesn't move when columns are inserted. MATCH on the header fixes that.

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/vlookup-column-number-from-match