Exact and Approximate Match

A lookup can match a key exactly or approximately. An exact match returns a value only when the key is identical and reports a missing-value error otherwise. An approximate match returns the nearest key instead, which means it returns something for almost any input. This entry uses VLOOKUP, where the final argument, range_lookup, selects the mode and approximate matching returns the largest key less than or equal to the value being looked up. Other functions set the direction differently; XLOOKUP, for example, takes a match_mode argument that can return the next smaller or the next larger item.

That difference decides correctness by data type rather than by taste. Approximate matching is the right tool for banded values — tax brackets, shipping tiers, grade boundaries — where “falls between these two numbers” is exactly the question. It is wrong for identifiers, where a near miss is not a match at all: an employee number one digit off should fail loudly, not quietly return the neighboring employee’s row.

In VLOOKUP, approximate matching also carries a precondition that is silent when broken. Google’s instruction is direct: “before you use an approximate match, sort your search key in ascending order. Otherwise, you may likely get a wrong return value,” and Microsoft warns that “if the first column isn’t sorted, the return value might be something you don’t expect”. An unsorted column does not produce an error; it produces a number. Google adds that with an approximate match, a key smaller than the smallest value in the first column returns a missing-value error. The sorted requirement belongs to the binary-search behavior of VLOOKUP’s approximate mode. XLOOKUP searches linearly by default, and only its binary search modes require the lookup array to be sorted.

Two practical consequences follow. Prefer exact matching by default and let unmatched keys announce themselves, since a visible error is cheaper than a plausible wrong value. And when approximate matching is genuinely wanted, treat the sorted band table as part of the formula’s correctness — if someone re-sorts or inserts a row later, the lookup keeps working and starts being wrong.

References:

• VLOOKUP function
• Google Sheets VLOOKUP