VLOOKUP not working: #N/A, wrong value or #REF!

1The fourth argument is missing (approximate match)

If you leave out range_lookup, VLOOKUP assumes TRUE: approximate match. It expects the first column to be sorted and returns the closest smaller value when there's no exact match. On unsorted data it can return values from the wrong row or #N/A for values that are there.

How to recognise it

The formula has only three arguments, like =VLOOKUP(F2,A2:C100,3), and you get a result for an ID that isn't in the list, or a result from the row above.

Fix

=VLOOKUP(F2,A2:C100,3,FALSE)

0 works the same as FALSE. Keep approximate match only for things like tax brackets or grade bands, where the first column is sorted on purpose.

2The table range isn't locked with $ signs

How to recognise it

The first rows work, but further down you get #N/A for values that exist. Click a lower formula: the table range has slid down, for example to A12:C111.

Fix

Lock the table range with $ signs. Select the range in the formula and press F4:

=VLOOKUP(F2,$A$2:$C$100,3,FALSE)

Prevent it. Turn the list into a table (Insert > Table) and refer to it by name, for example =VLOOKUP(F2,Products,3,FALSE). Table references don't slide and grow with new rows.

3col_index_num is wrong (#REF! or the wrong column)

The column number counts from the first column of the table range, not from column A of the sheet.

How to recognise it

• #REF!: the number is bigger than the number of columns in the range. =VLOOKUP(F2,A2:C100,4,FALSE) asks for column 4 of a 3-column range.
• #VALUE!: the number is less than 1.
• A value from the wrong column: the range starts at column B, but you counted from A.

Fix

Count the columns from the left edge of the range, or widen the range to include the column you want.

4A column was inserted or deleted in the table

A typed column number doesn't change when columns move. Insert a column inside the table range and 3 now points at a different column, so you get wrong values without any error.

Fix

Let MATCH find the column by its heading:

=VLOOKUP(F2,$A$2:$D$100,MATCH("Price",$A$1:$D$1,0),FALSE)

Or switch to XLOOKUP, which points at the result column directly (Excel 2021 and Microsoft 365):

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100)

5The value you want is to the left of the lookup column

VLOOKUP only searches the first column of the range and only returns columns to its right. It can't look up an email in column C and return a name from column A.

Fix

Use XLOOKUP:

=XLOOKUP(F2,$C$2:$C$100,$A$2:$A$100)

Or INDEX and MATCH, which work in every version:

=INDEX($A$2:$A$100,MATCH(F2,$C$2:$C$100,0))

6The values don't really match (spaces, text numbers)

With FALSE set and the ranges right, a remaining #N/A usually means the values differ in a way you can't see: an extra space, or a number stored as text in one place and a number in the other.

Fix

=VLOOKUP(TRIM(F2),$A$2:$C$100,3,FALSE)

For IDs stored as text, use F2&"" as the lookup value; for numbers, use --F2. The XLOOKUP #N/A guide covers these in detail, and the same fixes apply to VLOOKUP.

To show a message instead of #N/A for values that really aren't there:

=IFNA(VLOOKUP(F2,$A$2:$C$100,3,FALSE),"Not found")