How to stop VLOOKUP from breaking when columns are inserted or deleted
Formula (Excel and Google Sheets)
=VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE)Find the column number from its header instead of typing it: =VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE).
- Result on the example
- Armtek in
H3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in H3. Change any input and the result updates, or edit the formula to experiment.
How it works
A typed column number such as 5 doesn't change when someone inserts a column inside the table, so VLOOKUP quietly returns the wrong field, or #REF! after a deletion. MATCH(H2,A1:E1,0) finds the position of the "Supplier" header every time, so the formula follows the column wherever it moves.
=VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE)Result"Armtek"
H1"P-103"The value in H1.
A2:E87 rows × 5 cols: "P-101", "Desk lamp", $39.99, 140, …A range: 7 rows × 5 columns.
H2"Supplier"The value in H2.
A1:E11 row × 5 cols: "SKU", "Product", "Price", "Stock", …A range: 5 cells in one row.
MATCH(H2,A1:E1,0)5MATCH: Position of a value in a list. 0 = exact match.- lookup_value
H2"Supplier" - lookup_array
A1:E11 row × 5 cols: "SKU", "Product", "Price", "Stock", … - match_type
0
- lookup_value
VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE)"Armtek"VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.- lookup_value
H1"P-103" - table_array
A2:E87 rows × 5 cols: "P-101", "Desk lamp", $39.99, 140, … - col_index_num
MATCH(H2,A1:E1,0)5 - range_lookup
FALSE
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE). We ran it in Google Sheets mode on the same data and it returns Armtek.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:E8,MATCH(H1,A2:A8,0),MATCH(H2,A1:E1,0)) | Armtek | INDEX with two MATCHes picks the row and the column by name, in any version. |
| =XLOOKUP(H1,A2:A8,XLOOKUP(H2,A1:E1,A2:E8)) | Armtek | The inner XLOOKUP returns the whole column named in H2, and the outer one picks the row. |
Common mistakes
- The header in H2 must match the table header exactly, apart from case.
- Lock the header row and the table with $ before filling the formula across or down.
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.
Stop VLOOKUP from breaking when columns are inserted or deleted
H3, return the field named in H2 for the SKU in H1, finding the column by its header rather than a typed number.Questions
Does XLOOKUP have this problem?
No. XLOOKUP points at the return column itself, so inserting columns moves the reference with it.
Is this the same as a two-way lookup?
Yes: the row comes from H1 and the column from H2. INDEX with two MATCHes is the classic form.
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.