How to make the VLOOKUP column number dynamic in Excel

Q: 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).

Formula (Excel and Google Sheets)

=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)

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

Result on the example: $98,100 in I3
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in I3. Change any input and the result updates, or edit the formula to experiment.

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.

=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)

Result98100

I1"E-1046"
The value in I1.
A2:F87 rows × 6 cols: "E-1041", "Marcus Lee", "Finance", "Analyst", …
A range: 7 rows × 6 columns.
I2"Salary"
The value in I2.
A1:F11 row × 6 cols: "Employee ID", "Name", "Department", "Title", …
A range: 6 cells in one row.
MATCH(I2,A1:F1,0)6
MATCH: Position of a value in a list. 0 = exact match.
- lookup_valueI2"Salary"
- lookup_arrayA1:F11 row × 6 cols: "Employee ID", "Name", "Department", "Title", …
- match_type0
VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)98100
VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.
- lookup_valueI1"E-1046"
- table_arrayA2:F87 rows × 6 cols: "E-1041", "Marcus Lee", "Finance", "Analyst", …
- col_index_numMATCH(I2,A1:F1,0)6
- range_lookupFALSE

In Google Sheets

The same formula works in Google Sheets: =VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE). We ran it in Google Sheets mode on the same data and it returns $98,100.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=INDEX(A2:F8,MATCH(I1,A2:A8,0),MATCH(I2,A1:F1,0))	$98,100	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))	$98,100	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.

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.

Make the VLOOKUP column number dynamic

In I3, return the field named in I2 for the employee ID in I1. Don't type the column number.

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.

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.