VLOOKUP without the fourth argument

What each thing is

• VLOOKUP: looks for a value in the first column of a table and returns what is in another column of the same row. In Portuguese Excel, PROCV.
• The four arguments: the value to look up, the table, the number of the column to return and the fourth, optional one, which chooses the type of search (range_lookup in English and intervalo_pesquisa in Portuguese).
• Approximate match (TRUE): the default when the fourth argument is left empty. According to Microsoft, "If you don't specify anything, the default value will always be TRUE or approximate match." It expects the first column to be sorted.
• Exact match (FALSE, or zero): looks for exactly that value in the first column. If it does not find it, it returns #N/A (in Portuguese Excel, #N/D).

Build the sample spreadsheet

Made-up data, just for the test. The sheet names and headers stay in Portuguese, so the formulas are the same in both versions of this guide: Clientes means customers, Pedidos means orders, codigo means code and cliente means customer.

On the Clientes sheet, from A1 to B8, the first column is out of order on purpose:

codigo	cliente
1042	Ana Lima
1015	Bruno Reis
1088	Carla Dias
1023	Davi Nunes
1060	Elisa Prado
1037	Fabio Souza
1071	Gisele Alves

On the Pedidos sheet, column A, starting at A2, the order codes. Codes 1050 and 1099 do not exist on the Clientes sheet:

codigo
1042
1015
1088
1023
1060
1037
1071
1050
1099

Reproduce the problem

In B2, the formula without the fourth argument. Drag it down to B10.

=VLOOKUP(A2,Clientes!A$2:B$8,2)

In C2, the version with FALSE. Drag it down to C10.

=VLOOKUP(A2,Clientes!A$2:B$8,2,FALSE)

In Portuguese Excel: =PROCV(A2;Clientes!A$2:B$8;2) and =PROCV(A2;Clientes!A$2:B$8;2;FALSO).

Compare the columns

We tested the same spreadsheet in two programs. Code 1060 belongs to Elisa Prado.

Microsoft Excel 16.114.4 for Mac

LibreOffice Calc 24.2.7.2

The Excel test was run in Portuguese, where this error shows up as #N/D. Here it appears as #N/A, its name in English and in the LibreOffice used in the test.

Notice what repeats in both programs: correct results appear among the wrong ones, which is why the error goes unnoticed; codes that exist can return another customer’s name; and codes that do not exist return a real name instead of an error (1050 and 1099). LibreOffice also showed the opposite: code 1037, which exists, returned an error.

What changes from one program to the other is which wrong name comes back. With FALSE, both return the right result and flag the codes that do not exist. If your spreadsheet shows other names, the lesson is the same: the column without the fourth argument cannot be trusted.

The fix

Fill in the fourth argument with FALSE (or with zero), which Microsoft describes as exact match. To look up a code, an id or any key that has to match exactly, this is what you want.

Check it (do not skip this)

Count the #N/A values in the FALSE column. In the example there are two (1050 and 1099), the same number of codes that are not in the customer list.

=SUMPRODUCT(--ISNA(C2:C10))

Add a check column next to it. In the example, it should show one for each code that exists and zero for the one that does not.

=COUNTIF(Clientes!A$2:A$8,A2)

In Portuguese Excel: =SOMARPRODUTO(--É.NÃO.DISP(C2:C10)) and =CONT.SE(Clientes!A$2:A$8;A2). In the test in Excel 16.114.4 for Mac, the count was 2, and the check showed one for 1060 and zero for 1050. In LibreOffice 24.2, with the English formulas, the count was also 2 and the check showed zero only for 1050 and 1099.

• Sorting the first column does not replace FALSE in an exact search: the order only matters for approximate match, and a code that does not exist would still return a name. (Our conclusion, not on Microsoft’s page.)
• When VLOOKUP returns a name and you have no way of knowing whether it is the right one, cross-check a sample against the original table.

Alternative: XLOOKUP, when available

XLOOKUP (PROCX in Portuguese) uses exact match by default. On Microsoft’s page: "Exact match. If none found, return #N/A. This is the default."

=XLOOKUP(A2,Clientes!A$2:A$8,Clientes!B$2:B$8)

In Portuguese Excel: =PROCX(A2;Clientes!A$2:A$8;Clientes!B$2:B$8). In the test in Excel 16.114.4 for Mac, XLOOKUP returned Elisa Prado for 1060 and #N/A (#N/D in Portuguese) for 1050, even with the first column out of order. The LibreOffice 24.2 used in the test does not have this function. The same page says "XLOOKUP is not available in Excel 2016 and Excel 2019", but the "Applies to" list on the Portuguese version of the page includes those versions. The two pieces of information do not match: check in the version of Excel you use.

Rule of thumb

• Close it with FALSE: code, id or name, and anything that has to match exactly.
• Approximate: ordered range tables, such as commission brackets (our example), and only when you want the closest bracket.

In English Excel, the function is VLOOKUP and the separator is the comma. In Portuguese, PROCV with a semicolon. If the formula gives a syntax error when you paste it, check the language and the separator in your regional settings.

Sources

Microsoft Support pages, opened on November 10, 2026: VLOOKUP function, Função PROCV, XLOOKUP function and Função PROCX. Our own test in Microsoft Excel 16.114.4 for Mac and LibreOffice 24.2.7.2, with this guide’s sample data.