VLOOKUP TRUE vs FALSE: Approximate vs Exact Match

The short answer to VLOOKUP TRUE vs FALSE: the last argument chooses how VLOOKUP matches. FALSE finds an exact match, and TRUE finds an approximate match in a sorted list, meaning the closest value that is not bigger than the one you look up. Use FALSE when you look up IDs, names or codes, and TRUE when you look up a number in bands such as tax brackets or grades. This comparison is for anyone who has seen a VLOOKUP return the wrong row and wants to know why.

VLOOKUP TRUE vs FALSE at a glance#

FALSE asks "is this exact value here?"; TRUE asks "which band does this number fall into?".

The full syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The fourth argument, range_lookup, is the one this article is about. The VLOOKUP function guide covers the other three.

How each match type works#

Both settings use the same first three arguments; only the way VLOOKUP searches the first column changes.

FALSE: exact match#

With FALSE, VLOOKUP looks down the first column for a cell that equals the lookup value exactly, and returns the value from the column you name.

=VLOOKUP("P-110",A2:C5,3,FALSE)

The result is 18.00. The list is not sorted, and that doesn't matter. Look up "P-999" and you get #N/A, which is honest: the code isn't there. Upper and lower case don't matter, so "p-110" also matches.

FALSE also lets you use wildcards in text lookups. =VLOOKUP("Desk*",B2:C5,2,FALSE) returns 18.00, because * stands for any characters after "Desk". The VLOOKUP lesson practises exact-match lookups step by step.

TRUE: approximate match#

With TRUE, VLOOKUP assumes the first column is sorted from smallest to largest and returns the row with the largest value that is less than or equal to the lookup value. Think of the first column as the lower limit of each band.

=VLOOKUP(32000,A2:B5,2,TRUE)

The result is 6%. 32,000 isn't in the list, so VLOOKUP takes the largest limit that doesn't go over it: 25,000. Sales of exactly 25,000 also return 6%, and 9,999 returns 2%. Any amount of 50,000 or more returns 8%.

If the lookup value is smaller than the first value in the column, there is no band to fall into, and VLOOKUP returns #N/A. That's why tier tables usually start at 0. The approximate VLOOKUP lesson builds a table like this from scratch.

The same lookup with TRUE and FALSE#

Using the commission table above, here is what each match type returns:

FALSE only finds values that are exactly in the list. For bands, that's useless. Now go back to the product table and try TRUE:

=VLOOKUP("P-102",A2:C5,3,TRUE)

There is no P-102, but instead of #N/A you can get a price from another row, because TRUE returns "the closest one below". And since the codes aren't sorted, even codes that do exist can come back with the wrong price. There is no error to warn you, which is what makes this mistake so costly.

When to use FALSE#

Use FALSE for any lookup where the value must exist exactly. It fits:

• IDs and codes: employee numbers, product codes, order numbers.
• Names and emails, where a near miss would be a different person.
• Unsorted data, which is most real data.
• Wildcard searches, such as names that start with a certain word.
• Job tests and interviews. If the task says "find the price for this product", FALSE is the expected answer. The VLOOKUP interview questions cover this point often.

When to use TRUE#

Use TRUE when you place a number into a band. It fits:

• Tax brackets and pay scales.
• Grade boundaries, such as 0 = F, 50 = C, 70 = B, 85 = A.
• Discount, commission or shipping tiers.
• Date bands, such as which price list applies on a given date, when the start dates are sorted.
• Shipping by weight, where each row holds the starting weight of a price band and a parcel of 2.7 kg falls into the band that starts at 2 kg.
• Scoring rubrics, such as turning a satisfaction score of 0 to 100 into a label like Poor, Fair, Good or Excellent.

The rules are strict: the first column must hold the lower limit of each band, and it must be sorted from smallest to largest.

Common mistakes with the last argument#

1. Leaving it out. VLOOKUP then uses TRUE. On unsorted IDs, this returns wrong values. Always type FALSE for exact lookups.
2. Using TRUE on an unsorted list. Sort the first column ascending (Data > Sort A to Z), or switch to FALSE.
3. Putting upper limits in the first column. For TRUE, list where each band starts (0, 10,000, 25,000), not where it ends.
4. Expecting #N/A from TRUE. Approximate match returns the band below a missing value. That's correct for tiers but wrong for codes.
5. Numbers stored as text. With FALSE, the number 101 doesn't match the text "101". Convert one side so both are the same type.
6. Forgetting to lock the table. Before filling down, use $A$2:$B$5 (press F4 on Windows or ⌘T on a Mac).

How XLOOKUP handles the same choice#

XLOOKUP, in Excel 2021 and Microsoft 365, defaults to an exact match, so the risky default is gone. For bands, its fifth argument, match_mode, set to -1 means "exact match or next smaller item", like VLOOKUP with TRUE, and XLOOKUP doesn't need the list sorted for this. =XLOOKUP(32000,A2:A5,B2:B5,,-1) returns 6%. See VLOOKUP vs XLOOKUP and VLOOKUP vs INDEX MATCH for the full comparison, and VLOOKUP vs HLOOKUP if your table runs across rows.

Our recommendation#

Type FALSE every time, unless you are deliberately looking up a number in sorted bands. Exact match is what almost every lookup needs, and its #N/A tells you honestly when something is missing. Use TRUE for tax brackets, grades and tiers, with the lower limits sorted from smallest to largest. Never rely on the default.

In short: FALSE for things, TRUE for bands, and never leave the argument blank.

Practise it#

Try the exercise on this page: find a commission rate with an approximate match. Then practise exact-match VLOOKUP in a live spreadsheet and the approximate-match VLOOKUP lesson: you type each formula, and it checks your answer instantly.