Spreadsheet Practice

How to fix the #REF! error in VLOOKUP

Formula (Excel and Google Sheets)

=VLOOKUP(G1,A2:D9,4,FALSE)

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

Result on the example
210 in G2
Works in
Every current version
Google Sheets
Same formula

See it work

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

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.

=VLOOKUP(G1,A2:D9,4,FALSE)

Result210

  1. G1"P-105"

    The value in G1.

  2. A2:D98 rows × 4 cols: "P-101", "Desk lamp", $39.99, 140, …

    A range: 8 rows × 4 columns.

  3. VLOOKUP(G1,A2:D9,4,FALSE)210

    VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.

    • lookup_valueG1"P-105"
    • table_arrayA2:D98 rows × 4 cols: "P-101", "Desk lamp", $39.99, 140, …
    • col_index_num4
    • range_lookupFALSE

In Google Sheets

The same formula works in Google Sheets: =VLOOKUP(G1,A2:D9,4,FALSE). We ran it in Google Sheets mode on the same data and it returns 210.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(G1,A2:A9,D2:D9)210XLOOKUP has no column number to get wrong: you point at the stock column itself.
=VLOOKUP(G1,A2:D9,COLUMNS(A2:D2),FALSE)210COLUMNS counts the table's columns, so this always returns the last one.
=VLOOKUP(G1,A2:C9,4,FALSE)#REF!Different resultShown 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.

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.

Fix the #REF! error in VLOOKUP

In G2, return the stock (column D) for the SKU in G1 with VLOOKUP.

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

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.