Exact vs Approximate Match

The last argument of VLOOKUP, range_lookup, decides how the search behaves. It is small but powerful:

• FALSE (or 0) means exact match
• TRUE (or 1) means approximate match

Choosing the wrong one is one of the most common spreadsheet mistakes. This lesson shows exactly when to use each.

An exact match finds a value that matches precisely. If the value is not there, VLOOKUP returns the #N/A error rather than guessing.

Use FALSE when you are looking up unique identifiers like product codes, employee IDs, or email addresses, where only a perfect match is correct.

The table does not need to be sorted for an exact match. VLOOKUP scans until it finds the value.

With our price table (A100 Apple 0.50, B200 Banana 0.30, C300 Cherry 1.20), an exact lookup of an existing code works perfectly:

Returns 1.20. But if you search for a code that does not exist, like "Z999", you get #N/A. That error is actually useful: it tells you the item is truly missing instead of returning a wrong neighbor.

An approximate match finds the largest value that is less than or equal to your lookup value. It is built for ranges and bands, not exact IDs.

The classic use is a tier table: tax brackets, shipping bands, grade boundaries, or volume discounts, where a value falls somewhere between two thresholds.

One critical rule comes with TRUE, covered in the next scene.

For approximate match to work, the first column must be sorted in ascending order (smallest to largest). VLOOKUP walks down the column and stops at the last value that does not exceed your lookup value.

If the column is not sorted, TRUE returns unpredictable, wrong answers without any error. This silent failure is why many people avoid TRUE unless they specifically need banding.

Suppose a grading table sits in A1:B5, sorted ascending by minimum score:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

A score of 76 should return C, because 76 falls in the 70-79 band. The formula uses TRUE so it finds the highest threshold not exceeding 76:

With a lookup value of 76 and TRUE, VLOOKUP reads down the sorted thresholds: 0, 60, 70, 90... It compares each one.

• 0 is less than or equal to 76: keep going
• 60 is less than or equal to 76: keep going
• 70 is less than or equal to 76: keep going
• 90 is greater than 76: stop

It backs up to the last valid row (70) and returns its band: C. Approximate match is really a between-thresholds lookup.

If you leave out the fourth argument entirely, VLOOKUP defaults to TRUE (approximate). This surprises many people who expect exact behavior.

A formula like =VLOOKUP(A2, Data!A:B, 2) on an unsorted list can return a wrong value silently. The safe habit: always type FALSE unless you are deliberately doing band matching on a sorted table.

Here is the difference at a glance:

• FALSE / exact: table need not be sorted, missing value returns #N/A, best for IDs and codes
• TRUE / approximate: table must be sorted ascending, never returns #N/A for in-range values, best for tiers and bands

Pick based on the question you are answering: "is this exact item present?" uses FALSE; "which band does this fall into?" uses TRUE.

A weight in D2 needs a shipping cost from a sorted band table in A2:B6 (0, 1, 5, 10, 20 kg thresholds). Approximate match picks the right tier:

If D2 is 7, it lands in the 5 kg band and returns that band's cost. Change the weight and the tier updates instantly, with no list of every possible weight needed.

There is a subtle performance angle too. On very large sorted tables, an approximate match (TRUE) can be faster because the spreadsheet can jump quickly through sorted values rather than scanning every row.

But speed never beats correctness. If your data is not sorted, or you need exact IDs, always choose FALSE. A fast wrong answer is worse than a slightly slower right one. For modern spreadsheets and typical table sizes, the difference is rarely noticeable, so default to FALSE for safety.

Choose the right match type for the situation.

Key takeaways:

• FALSE = exact match, table unsorted is fine, missing values return #N/A
• TRUE = approximate match, first column must be sorted ascending, finds the largest value not over your target
• Omitting the argument defaults to TRUE, so always state it
• Use exact for IDs and codes; use approximate for tiers and bands

Next you will flip the direction and search across rows with HLOOKUP.