VLOOKUP (CERCA.VERT in the Italian version) is probably the most used function after SUM, and also one of the most misunderstood. It does one thing: given a value, it finds it in the first column of a table and returns the content of another column on the same row. The classic example is a price list: you type a product code and get the description or the price. It works in Excel, LibreOffice Calc and Google Sheets with the same logic.

The syntax, argument by argument

=VLOOKUP(value, table, column_index, range_lookup)

1. value: what you are looking for, for example the code in A2.
2. table: the range holding the data, for example PriceList!$A$2:$D$500. The search always happens in the first column of this range, and only there.
3. column_index: the number of the column to return, counted from the first column of the table. If the table starts at A and you want the price in D, the index is 4.
4. range_lookup: FALSE (or 0) for an exact match, TRUE (or 1) for an approximate one. If you leave it out, it defaults to TRUE.

A typical formula therefore looks like this:

=VLOOKUP(A2, PriceList!$A$2:$D$500, 4, FALSE)

Note the dollar signs in the table range: if you copy the formula down without locking it, the table slides along with the formula and the last rows stop finding their data.

The fourth argument is the one that matters

The most widespread mistake is forgetting the fourth argument. With an approximate match the program assumes the first column is sorted in ascending order and, if it does not find the exact value, it returns the row with the largest value below the one you searched for. On an unsorted list of codes the result is a plausible but wrong figure, with no error message at all.

The practical rule is simple: for codes, names, invoice numbers and any kind of identifier, always use FALSE.

When approximate match is useful

There is one case where TRUE is exactly what you need: looking up bands. Picture a table of quantity discounts:

With =VLOOKUP(B2, $F$2:$G$5, 2, TRUE) a quantity of 340 returns 10%, because 200 is the largest value not exceeding 340. The same pattern works for commission tiers, grades and age brackets. The first column, however, must be sorted from smallest to largest.

The structural limits

VLOOKUP has some constraints that do not depend on how you write it.

• It only looks to the right. The value must be in the first column of the table; you cannot search for a name in column C and return the code in column A.
• The column index is a fixed number. If someone inserts a column in the price list, index 4 now points to different data and the formula keeps working, but returns the wrong column.
• It only returns the first match. If a code appears twice, the second row is ignored.
• It is not case sensitive. "abc" and "ABC" are treated as equal.

Why it cannot find a value that is there

The #N/A message (#N/D in Italian) means the value was not found. Often, though, the value is there but not identical. The most frequent causes are:

• Extra spaces, at the start or end of the text, typical of data copied from business software or web pages. TRIM removes them.
• Numbers stored as text on one side and as numbers on the other. The code 1050 as a number and "1050" as text do not match.
• Invisible characters, such as the non-breaking space that comes from web pages.

A quick check: in an empty cell type =A2=PriceList!A17, comparing the value you are searching for with the one you see in the table. If the result is FALSE, the two values are different even though they look the same.

Handling errors without hiding them

To show some text instead of #N/A, people often use IFERROR:

=IFERROR(VLOOKUP(A2, PriceList!$A$2:$D$500, 4, FALSE), "Not found")

It works, but it masks every error, including those caused by a broken reference. IFNA is more precise: it only catches the "not available" value and leaves other problems visible. In my work I prefer showing an explicit message to an empty cell: a silent blank is easily mistaken for a zero.

When to move on

If you use a recent version of Excel or Google Sheets, XLOOKUP solves almost every limit listed above: it searches in any direction, does not rely on numeric indexes and uses exact match by default. Alternatively, the INDEX and MATCH combination works everywhere, including older versions and LibreOffice. VLOOKUP is still worth knowing, because you will find it in countless spreadsheets written by other people.

In short

• VLOOKUP searches the first column of the table and returns a column to its right.
• For identifiers always use FALSE as the fourth argument.
• Approximate match is for bands, with the first column sorted.

• N/A often comes from spaces, text versus number, or invisible characters.

• For sturdier lookups consider XLOOKUP or INDEX and MATCH.