# How to fix the #REF! error in VLOOKUP

The column number is bigger than the table is wide. Count from the table's first column, or widen the table: =VLOOKUP(G1,A2:D9,4,FALSE).

**Excel:** `=VLOOKUP(G1,A2:D9,4,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 |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock |  | SKU | P-105 |
| P-101 | Desk lamp | 39.99 | 140 |  | Stock |  |
| P-102 | Office chair | 249 | 32 |  |  |  |
| P-103 | Monitor arm | 89.5 | 75 |  |  |  |
| P-104 | Standing desk | 449 | 18 |  |  |  |
| P-105 | Keyboard | 74.95 | 210 |  |  |  |
| P-106 | Webcam | 59 | 96 |  |  |  |
| P-107 | Headset | 129 | 54 |  |  |  |
| P-108 | Laptop stand | 45 | 160 |  |  |  |

Formula in G2: `=VLOOKUP(G1,A2:D9,4,FALSE)`

Result: 210

## How it works

VLOOKUP's third argument counts columns inside the table you give it, starting at 1. With the table `A2:C9` there is no column 4, so asking for it returns #REF!. Widening the table to `A2:D9` makes Stock column 4.

## Other ways

- `=XLOOKUP(G1,A2:A9,D2:D9)`: XLOOKUP has no column number to get wrong: you point at the stock column itself.
- `=VLOOKUP(G1,A2:D9,COLUMNS(A2:D2),FALSE)`: COLUMNS counts the table's columns, so this always returns the last one.
- `=VLOOKUP(G1,A2:C9,4,FALSE)`: Shown for contrast: the table is one column too narrow. (#REF!, because A2:C9 has only 3 columns.)

## Common mistakes

- A column number of 0 or less returns #VALUE!, not #REF!.
- Deleting a column from the sheet can leave the number pointing past the table. XLOOKUP, or a MATCH on the header, avoids this.
- If the table reference itself shows #REF! inside the formula, the cells it pointed to were deleted. Retype the range.

## Questions

**How do I work out the column number?**

Count from the table's first column, not from column A of the sheet: in `C2:F9`, column E is 3.

**Can the column number come from the header?**

Yes: `=VLOOKUP(G1,A2:D9,MATCH("Stock",A1:D1,0),FALSE)`.

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-ref-error