# How to stop VLOOKUP from breaking when columns are inserted or deleted

Find the column number from its header instead of typing it: =VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE).

**Excel:** `=VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,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 |
| --- | --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock | Supplier |  | SKU | P-103 |
| P-101 | Desk lamp | 39.99 | 140 | Lumio |  | Field | Supplier |
| P-102 | Office chair | 249 | 32 | Seatco |  | Result |  |
| P-103 | Monitor arm | 89.5 | 75 | Armtek |  |  |  |
| P-104 | Standing desk | 449 | 18 | Deskworks |  |  |  |
| P-105 | Keyboard | 74.95 | 210 | Keyco |  |  |  |
| P-106 | Webcam | 59 | 96 | Lumio |  |  |  |
| P-107 | Headset | 129 | 54 | Soundly |  |  |  |

Formula in H3: `=VLOOKUP(H1,A2:E8,MATCH(H2,A1:E1,0),FALSE)`

Result: Armtek

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

## Other ways

- `=INDEX(A2:E8,MATCH(H1,A2:A8,0),MATCH(H2,A1:E1,0))`: 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))`: 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.

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

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-breaks-when-columns-are-inserted