VLOOKUP Exact Match: Why FALSE Matters

A VLOOKUP exact match finds the value you ask for, or tells you it isn't there with #N/A. You get it by typing FALSE as the fourth argument: =VLOOKUP(E2,A2:C6,3,FALSE). Leave FALSE out and VLOOKUP switches to an approximate match, which can return someone else's data without any warning. This guide shows that mistake on real numbers, then covers the rules of exact matching and how to fix #N/A.

What the fourth argument of VLOOKUP does#

VLOOKUP has four arguments, and the last one decides how it matches:

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

The square brackets mean the argument is optional, and when it is left out, Excel uses TRUE. That default is the root of the problem: for most lookups, such as IDs, product codes, emails or names, you want an exact match. The beginner guide to VLOOKUP step by step explains the first three arguments.

VLOOKUP exact match example#

Here is a staff table in A1:C6, sorted by ID:

With 104 in E2, type this in F2:

=VLOOKUP(E2,A2:C6,3,FALSE)

VLOOKUP looks down column A for exactly 104, finds it on row 4, and returns the 3rd column: 61000, Lina's salary. The column number counts from the first column of the table; see the VLOOKUP column index number if that part is unclear.

Why leaving out FALSE gives wrong answers#

Now look up an ID that doesn't exist. There is no employee 103:

Without FALSE, VLOOKUP does an approximate match. It finds the largest ID that is less than or equal to 103, which is 102, and returns Tom's salary as if it belonged to employee 103. There is no error and nothing looks wrong, which is why this mistake survives into real reports.

On an unsorted table, approximate match is even less predictable: it can return a wrong row or #N/A for a value that is actually there. Exact match doesn't care about order, so it works on any table.

The #N/A from an exact match is useful information: it tells you the ID is missing. If you'd rather show a message, wrap the formula in IFNA: =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found").

When approximate match is the right choice#

Approximate match has one proper job: putting a number into a band, such as a tax bracket, a commission tier or a letter grade. There, a score of 78 should match the band that starts at 70, so "the largest value less than or equal to" is exactly what you want. The table must be sorted from smallest to largest. The guide to VLOOKUP approximate match for tiers covers this case.

A simple rule: if you are looking up an identifier (an ID, code, name or email), use FALSE. If you are looking up a number to find its band, use TRUE, and type it out so the next person knows it was deliberate.

Exact match rules you should know#

Exact match is strict in some ways and relaxed in others:

1. Capitals don't matter. "ana" finds "Ana". VLOOKUP is not case-sensitive.
2. Spaces do matter. "Ana " with a trailing space doesn't match "Ana". Clean the data with TRIM.
3. Numbers and text are different. The number 104 doesn't match the text "104". Numbers imported as text are a classic cause of #N/A.
4. The first match wins. If ID 104 appears twice, VLOOKUP returns the first row it finds and ignores the second.
5. Wildcards work with text. With FALSE, "Gr*" finds the first value starting with Gr (Grace). Use ~* to look up a real asterisk.

To bring back several columns for the same match, such as name and salary together, see VLOOKUP for multiple columns.

How to fix #N/A when the value looks like it's there#

When an exact match returns #N/A but you can see the value in the table, the two values differ in a way you can't see. Check these in order:

1. Extra spaces. Compare lengths with =LEN(E2) and =LEN(A4). If they differ, use TRIM on the data, or look up TRIM(E2).
2. Number stored as text. Text numbers usually sit on the left of the cell and may show a green triangle. Convert them with Convert to Number or VALUE. Or match the type in the formula, for example VALUE(E2) when the table holds numbers.
3. Wrong table start. The lookup value must be in the first column of the table range. B2:C6 would search the Name column, not the ID column.
4. The table moved. If the formula was filled down without $ signs, the table range shifted. Lock it: $A$2:$C$6.

Exact match with XLOOKUP and INDEX MATCH#

Newer lookups make exact match the default, so you can't forget it. In Excel 2021 or Microsoft 365, XLOOKUP is exact unless you ask otherwise:

=XLOOKUP(E2,A2:A6,C2:C6)

INDEX with MATCH works in every version; MATCH's third argument, 0, means exact match:

=INDEX(C2:C6,MATCH(E2,A2:A6,0))

Both return 61000 for ID 104, and both can look to the left of the lookup column, which VLOOKUP can't. The comparison of VLOOKUP vs INDEX MATCH explains when each is the better tool.

Practise it#

Typing FALSE every time is a habit worth building now, because job tests check for it. Practise VLOOKUP in a live spreadsheet: you type the formula and the lesson checks it instantly. The VLOOKUP function guide is a quick reference, and the exercise on this page lets you look up employee 104 right away. To test yourself under time pressure, try the intermediate Excel test.