Why does VLOOKUP return #N/A?

The value is not found: missing, written differently (extra space, number stored as text), or outside a range that slid. See the #N/A error.

How do I look to the left?

With XLOOKUP, or with INDEX and MATCH: =INDEX(A:A,MATCH(E2,C:C,0)) returns column A for the value found in C.

The VLOOKUP function

The most used function after SUM: it looks for a value in the first column of a table, and returns what sits on the same row, in another column.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In a French Excel: =RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; [valeur_proche]).

Arguments

lookup_value: what you are looking for, often a cell (A2).
table_array: the range to search; the value is looked for in its first column. Lock it with $.
col_index_num: the number of the column to return, 1 being the first column of the range.
range_lookup: FALSE for an exact lookup. Omitted or TRUE, the lookup is approximate, on a sorted first column.

An example

=VLOOKUP(A2,Customers!$A$2:$D$500,3,FALSE)

Looks for the customer code in A2 in column A of the Customers sheet, and returns the value from the 3rd column of the range (column C) on the row found. #N/A if the code is not there.

Pitfalls

Forgetting FALSE: without a fourth argument, VLOOKUP looks for an approximate value and can return a wrong result with no error.
The sliding range: without $, copying the formula down shifts the range one row per cell.
The hard-coded column number: inserting a column in the range makes it return the wrong column, silently.
It only looks to the right: the returned column must be to the right of the lookup column.
Spaces and numbers stored as text: the usual cause of #N/A when the value seems to be there.

Going further

XLOOKUP, which has none of these flaws, in Excel 2021 and Microsoft 365.
INDEX and MATCH, the combination that works in every version.
Look up between two files: VLOOKUP between two files, with no formula.
Explain a formula: spots the pitfalls in your VLOOKUP.

Frequently asked questions

Why does VLOOKUP return #N/A?: The value is not found: missing, written differently (extra space, number stored as text), or outside a range that slid. See the #N/A error.
How do I look to the left?: With XLOOKUP, or with INDEX and MATCH: =INDEX(A:A,MATCH(E2,C:C,0)) returns column A for the value found in C.