Established onExcel, all versions: VLOOKUP and HLOOKUP default to approximateExcel, all versions: MATCH defaults to approximateGoogle Sheets, web: the same defaults
The last argument of the classic lookup functions says whether the match must be exact. Omitted, it means approximate, and approximate does not mean close enough. It means the largest key smaller than the one you asked for.
What that produces
Looking up a key that is not in the table returns a real value from a different row. Not an error, not a blank: a plausible number belonging to somebody else.
The behaviour is also undefined if the key column is not sorted ascending. It performs a binary search, which assumes order, and on unsorted data it returns whatever that search happens to land on. The result varies with the arrangement of the data rather than with its content.
Why the default is that way
Not carelessness. Approximate matching uses a binary search and is enormously faster than scanning every row, and on the hardware these functions were designed for, against sorted tables, that difference decided whether a sheet was usable.
The convention of sorted reference tables was normal then, and the fast path was made the default because it was the expected case. What changed is that data now arrives unsorted from other systems, and the default outlived the assumption behind it.
The one legitimate use
Banded lookups, and they are common. A table of thresholds — a tax band, a discount tier, a shipping weight bracket — is precisely a lookup where you want the largest key not exceeding the value. Approximate matching is the correct and elegant tool for that, and it is the reason the mode should not be described as simply wrong.
The distinction is whether the key column is a set of exact keys or a set of lower bounds. Where it is bounds, approximate is right. Where it is keys, approximate turns a missing key into somebody else's answer.
What to do
Pass the argument explicitly, every time. Even where you want approximate matching, writing it down says to the next reader that it was a decision. An omitted argument is indistinguishable from a forgotten one.
Prefer the newer function where available, whose default is exact and which takes an explicit value to return when nothing is found, so the absent case is handled in the formula rather than discovered later.
Handle absence deliberately. Wrapping a lookup so that a missing key becomes a blank hides the problem as effectively as approximate matching does. Where a key should always be present, a missing one is a fact about the data and should be visible: return a marker, and count the markers in a cell at the top of the sheet.
The related default in the matching function
The function that finds a position takes the same kind of argument and also defaults to approximate, in the same direction, with the same requirement that the column be sorted. It is easy to write the matched pair carefully and still inherit the behaviour by omitting the third argument of the match.
This is the commonest way for a carefully built lookup to carry the defect it was built to avoid, and it is invisible in reading because the argument that would reveal it is the one that is not there.
What we cannot verify
The defaults, the sorting requirement and the undefined behaviour on unsorted data are documented by both vendors and demonstrable directly. The performance rationale is our reading of why the default was chosen, supported by the search method being documented, and we have not found a contemporaneous statement of intent from the period when the default was set.
Why wrapping it in an error handler is not the fix
The common repair is to catch the error and show a blank or a zero. In exact mode that converts a visible failure into an invisible one, and a zero flowing into a sum is the worst of the available outcomes because it changes the total without appearing anywhere.
Catch the error by all means, and put something in the cell that a person will notice, and count those somethings where the result is used.
The one-cell check for this entire entry
Count the lookup results that came back as the marker for absent, and put that count where somebody will see it. In a table where every key should be present, any number other than zero is the whole story.
Reading an inherited formula
Count the commas. A lookup with three arguments where the fourth is absent is the case this entry is about, and it is faster to spot by the missing comma than by reading the whole expression.
In a column of thousands, the formula is the same in every row, so this is a single reading rather than an audit.
In short
- Omitting the last argument means approximate, which returns the largest smaller key.
- A key that is absent returns a real value belonging to another row.
- On unsorted data the result depends on arrangement rather than content.
- The default exists because binary search was much faster on sorted tables.
- Banded lookups are the legitimate use, where the column holds bounds rather than keys.
- The matching function has the same default, which is how careful formulas inherit the defect.