A lookup match mode defines how a spreadsheet function chooses an exact or approximate result. A valid formula and plausible output do not establish that the selected row matches the intended rule.
Substantively revised October 21, 2026. Author interpretation, documented cases and hypothetical examples are distinguished below.
The generated formula returns a plausible department name. There is no error marker, the workbook calculates, and the explanation sounds confident. Yet the requested employee identifier is absent from the table. A lookup can return a value without answering the question the spreadsheet was meant to ask.
Direct answer
Write the intended matching rule before accepting an AI-written lookup. Decide whether the key must match exactly or belongs in a threshold bracket, then inspect the actual function arguments and test synthetic cases for a known hit, a missing key, an intermediate threshold and a duplicate key. Check data types, whitespace, ranges and function availability separately. A calculated value proves execution under the formula's rules, not agreement with the business rule.
This article proposes a Lookup Boundary Set for reviewing a single lookup rule. It is an author-designed test method, not a claim that AI-generated formulas have a particular error rate. The method applies whether the formula came from an assistant, a colleague or an old workbook. AI authorship identifies its origin; the function and data determine its behavior.
Key takeaways
An omitted argument is still a choice when the function has a default. Excel VLOOKUP and XLOOKUP do not share the same default matching behavior. Read the specific function and arguments instead of carrying an assumption from one familiar formula to another.
Exact matching and unique matching are different requirements. An exact lookup can find one of several rows with the same key. If the business rule needs a unique record, investigate duplicates instead of treating the first returned value as proof of uniqueness.
Keep missing results visible during review. A friendly label can be appropriate for presentation, but it must not hide a lookup failure behind a value that resembles valid data. A blank or zero replacement can be especially ambiguous if either is also a legitimate result.
Use small, known fixtures before applying the formula broadly. The expected answer must come from the stated rule and synthetic table, rather than from another copy of the same generated formula. Otherwise two matching outputs can simply reproduce the same mistake.
Observation: plausible output is a weak test
A lookup failure is easy to notice when it produces an error. It is harder to notice when it produces a nearby row's label. The latter can pass a quick visual inspection because the value belongs to the right general category and resembles the expected answer.
The pattern is a mismatch between the rule a reader imagines and the rule the function executes. A request such as find the department for this employee suggests identity matching. A formula using approximate behavior can answer a different question about position in an ordered table. Both can calculate successfully while only one fits the request.
Define a lookup key as the value used to search the selected range. Define a match mode as the rule that determines whether a row qualifies. Define a duplicate policy as the intended treatment when several rows qualify. A return column is the field taken from the selected row. These entities need separate checks because correcting one does not automatically correct the others.
The review concerns a precise spreadsheet decision. It does not establish a general AI approval process, cure decision fatigue or demonstrate a productivity improvement. Its useful result is a formula whose matching behavior can be explained against a known requirement and a bounded set of tests.
Root cause: the formula silently completes an underspecified request
A short instruction often omits what should happen when a key is missing, duplicated or represented differently. A formula generator must still produce concrete syntax. Defaults and convenient error handling can fill those gaps without establishing that the choices were authorized by the workbook's purpose.
The spreadsheet adds another layer of ambiguity. Numeric-looking identifiers can be stored as text. Visible strings can contain surrounding spaces. A copied formula can shift a range. A correct match mode cannot repair every disagreement between intended identity and the actual searchable values.
The first task is therefore to define the relation. Does identifier 250 mean the record whose identifier is exactly 250, or the bracket that begins at the greatest eligible threshold below it? The same numbers can support either task. The label and business context determine the requirement; the function documentation determines how to implement it.
Do not assume that sorting is a universal repair. Sorting can be part of a valid approximate lookup, but it does not convert an identity problem into a threshold problem. It can also change which duplicate appears first. Preserve the intended row relationships and inspect the relevant source table before reorganizing it.
Three documented function examples
Microsoft documents that VLOOKUP uses approximate matching when range_lookup is omitted and requires the relevant ordering for that behavior. FALSE requests exact matching. This is a concrete default to inspect in a generated formula, not evidence that approximation is always wrong. A threshold lookup can intentionally need that different rule. [S1]
Microsoft documents XLOOKUP with exact matching as its default, alongside match-mode and search-mode options. Its availability note explicitly excludes Excel 2016 and Excel 2019. Do not infer availability from a broad page heading or assume that exact default behavior resolves duplicate records, source errors or compatibility with every workbook recipient. [S2]
Google Sheets documents VLOOKUP's omitted is_sorted argument as approximate matching and describes sorting, first-match behavior and whitespace-related pitfalls. That establishes specific Sheets behavior, not a universal rule for every spreadsheet function. Inspect the exact function used rather than transferring an argument position or default from another application. [S3]
These examples are product documentation from two providers. They explain mechanisms rather than compare AI systems or measure formula reliability. The synthetic test set below translates the mechanisms into questions a workbook reviewer can answer without making empirical claims about generated-code accuracy.
Framework: the Lookup Boundary Set
The set contains Hit, Gap, Boundary and Multiplicity. These are the author's names for four diagnostic cases. Each tests a different assumption. Passing the hit case alone is insufficient because an approximate and an exact formula can agree whenever the requested value happens to exist in a simple table.
Hit uses a known key and a known return value. It checks the chosen search range and return field. Make the return values distinctive enough to expose a column-selection mistake; a table containing repeated generic labels provides less useful evidence.
Gap uses a key absent from the intended identity table. Specify the expected missing outcome in advance. If the formula supplies a neighboring record or silently substitutes a legitimate-looking value, inspect the match rule and error handling. The gap case asks whether absence remains distinguishable from success.
Boundary uses an input between thresholds when a threshold interpretation is relevant. It helps distinguish a deliberate bracket rule from accidental approximation. State whether lower bounds are inclusive, what happens below the first threshold and what happens above the last. Those meanings come from the application, not from an arbitrary numeric example.
Multiplicity introduces a repeated key with distinguishable return values. The purpose is not to force one universal duplicate solution. It is to reveal whether the function returns one match while the requirement actually needs rejection, a selected latest record, aggregation or another explicit rule.
Keep a separate representation probe for type and whitespace when those risks apply. A text identifier and a numeric quantity have different meanings even if they look similar. Decide whether normalization is appropriate before changing source data. Automatically trimming or converting everything can destroy meaningful distinctions in a real identifier system.
The acceptance statement should identify the lookup relation, supported workbook environment and missing or duplicate policy. A successful fixture supports that bounded statement. It does not prove that an uninspected source table is complete, authoritative or free from every future update problem.
Hypothetical case study: an absent identifier finds a real label
Consider an invented two-column table in A2:B4. Column A contains the synthetic numeric identifiers 100, 200 and 300 in ascending order. Column B contains the distinct labels Cedar, Birch and Maple. The requested business rule is exact identifier lookup. These values are fictional fixtures, not employee data or measured outcomes.
An assistant proposes =VLOOKUP(E2,A2:B4,2). With E2 set to 200, the expected label is Birch, so the initial happy-path check looks convincing. The formula has omitted the matching argument. A reviewer who tests only that existing key has not distinguished the default approximate behavior from the intended exact relation.
The gap case sets E2 to 250. Under the documented approximate behavior on this sorted fixture, the formula can select the preceding threshold row and return Birch. That label is real within the fixture, but identifier 250 does not exist there. For the stated identity requirement, the result is misleading rather than a successful answer.
The reviewer changes the formula to =VLOOKUP(E2,A2:B4,2,FALSE) for the exact identity requirement. The missing identifier should now remain a missing match rather than silently borrowing another row's label. Presentation handling can later express that outcome clearly, but it should not replace the gap with a value that looks like a valid department.
Now suppose a different task uses the same numbers as lower thresholds for an explicitly defined classification. In that task, 250 belongs to the bracket beginning at 200. Approximate behavior can be intentional under the required ordering and boundary policy. The earlier identity correction should not be copied into this threshold task without reconsidering its meaning.
Multiplicity is tested with a separate synthetic table containing two rows for key 200, one labeled Birch and the other Pine. The reviewer does not simply expand the original lookup range and declare whichever label appears correct. The requirement must say whether duplicate identifiers are invalid or whether some independent rule chooses between records.
An XLOOKUP alternative may make the match choice clearer in a supported Excel environment, but changing the function does not eliminate that duplicate decision. Nor does it settle whether a text-form identifier is intended to equal a numeric value. These are separate requirements that must remain visible after the syntax improves.
The case's numerical identifiers, two-column arrangement and formula arguments are illustrative. No workbook has been claimed to perform a measured production test here. A reader should reproduce the fixture in the intended spreadsheet application and version, then record actual results before accepting a consequential workbook change.
The review concludes with a specific decision: use exact matching for the identity table, preserve a distinguishable missing result, and resolve duplicate identity records before treating the lookup as authoritative. The threshold task receives its own definition and tests. One formula is not promoted into a universal answer for both relationships.
Failure example: the missing result becomes zero
A workbook wraps a lookup in broad error handling that returns zero whenever something goes wrong. Zero is also a valid value in the return column. The sheet now appears clean, but a reviewer cannot distinguish a real zero from a failed lookup by looking at the displayed result alone.
The repair begins by identifying the intended missing outcome and the errors the formula is supposed to handle. Do not convert every failure into a success-shaped value. Preserve diagnostics during review, then choose presentation that lets the consuming process distinguish missing information from a genuine numeric result.
A related failure is asking the assistant to explain why its own formula is correct and accepting that explanation as independent verification. The explanation may describe the same mistaken assumption. Compare the syntax with official function documentation and evaluate known fixtures whose expected results were established separately.
Tradeoff and counterargument: exact matching is not always better
Exact matching is a suitable choice when the rule is identity. It is not inherently superior for every lookup. A well-defined bracket table can need an ordered threshold relation. Replacing all approximate formulas with exact formulas can break that legitimate use while making the spreadsheet look more cautious.
A counterargument is that modern functions default to exact matching, so match-mode review is unnecessary. Defaults remove one omission risk but leave range selection, duplicates, explicit overrides and missing-result handling. The relevant question is the actual formula's behavior, not the reputation of its function name.
Compatibility also matters. A formula available to the author may be unavailable to another recipient's installed application. Check the intended environment before replacing a working compatible expression solely for stylistic preference. A correct formula that cannot calculate where needed has not met the delivery requirement.
The test set should remain proportionate. A small low-impact lookup may need a concise fixture. A workbook driving an important decision can require broader validation of data lineage and downstream calculations. The Lookup Boundary Set examines matching behavior; it is not a substitute for every control appropriate to the workbook.
Common mistakes
Testing only a known key selects an easy case that can hide the disputed rule. Include absence or a relevant threshold so that the formula can demonstrate the difference between matching interpretations. Choose tests for their diagnostic value rather than their ability to pass.
Treating the first match as proof of unique identity overlooks duplicate records. Inspect the source and define the intended duplicate policy. Returning a label successfully does not prove that only one row qualified or that the first qualifying row was the correct business choice.
Sorting without preserving row relationships can corrupt the table while apparently satisfying an approximate lookup's ordering requirement. Use the application's appropriate data operation and inspect the result. Sorting one key column independently is not equivalent to maintaining an ordered record table.
Applying automatic type conversion to identifiers can create unintended equivalences. Establish whether a value represents a number or an identity string. Normalize only under an explicit rule, and retain enough evidence to detect when that transformation changes meaning.
Finally, copying a formula down without checking its range can change the searchable table. Inspect the intended references and test more than the first output cell. Correct match mode cannot compensate for searching a different set of rows than the requirement specifies.
Action plan
Choose one generated lookup and write its intended relation in plain language. State whether the key is an identity or a threshold input, how missing keys should appear and what duplicates mean. Identify the spreadsheet environment where the workbook must calculate.
Read the actual arguments against the official reference for that function. Build a small synthetic fixture with distinctive outputs and the relevant Hit, Gap, Boundary and Multiplicity cases. Establish expected results from the rule before running the formula, and keep representation probes separate when type or whitespace matters.
Inspect any error-handling wrapper and the exact ranges used when the formula is copied. Correct the responsible assumption and rerun the same fixture. Retain the observed results alongside the requirement rather than storing only a confident explanation from the formula's author.
Before applying the change to consequential data, review the source table and downstream use at the appropriate scope. The fixture demonstrates selected matching behavior. It does not certify the accuracy of every row or authorize using the output for an unrelated decision.
FAQ
Does VLOOKUP default to exact matching in Excel?
No. Its omitted matching argument uses approximate behavior under the documented rules. Inspect the actual argument rather than assuming that a familiar lookup name means exact identity matching.
Is XLOOKUP's default the same?
No. Microsoft documents exact matching as its default. Still inspect explicit modes, ranges and duplicate behavior instead of assuming that this default approves the whole formula.
Is approximate matching always a mistake?
No. A deliberate ordered threshold task can require it. The mistake is using a rule that differs from the intended relationship, or failing to satisfy its relevant ordering and boundary conditions.
Why does a missing identifier return a plausible value?
An approximate formula can select a qualifying nearby threshold row under its rules. Inspect the actual mode and table before treating a non-error value as evidence of exact identity.
Does exact matching prove that the source key is unique?
No. Multiple rows can contain the same key. Define and test the duplicate policy separately instead of accepting the first returned value as proof of uniqueness.
Should missing results always be blank?
No universal presentation works for every workbook. Choose an outcome distinguishable from valid data and understood by downstream consumers. Keep diagnostic information available during review.
Is replacing errors with zero safe?
Only under a justified interpretation. If zero is a real result, that replacement can conceal missing or failed lookups. Do not make different states indistinguishable merely to remove visible errors.
Can sorting fix every lookup problem?
No. Ordering can support a threshold rule, but cannot repair the wrong relation, duplicate policy or return field. Preserve complete row relationships when changing a table's order.
Can text and numeric keys be assumed equivalent?
No. Inspect their intended meaning and actual representation. A conversion appropriate to a quantity can be inappropriate for an identifier, especially when formatting carries identity information.
Why test whitespace separately?
Visible strings can hide surrounding spaces. Decide the normalization rule explicitly and inspect actual inputs; avoid treating an apparent visual match as proof of the same searchable value.
Is XLOOKUP available in Excel 2016 and 2019?
Microsoft's function page explicitly says it is unavailable in those versions. Check recipient compatibility rather than relying on a broad Applies To list or the author's newer installation.
Does the AI's explanation count as independent review?
It can help locate assumptions, but is insufficient verification by itself. Compare actual syntax, primary documentation and separately defined expected fixture results.
Can one passing cell approve a copied formula column?
No. References and source coverage can change when copied. Inspect the intended ranges and representative later cells as well as the first successful lookup.
Must the fixture contain real customer records?
No. Synthetic keys and distinctive labels can expose matching behavior without private data. Production-data accuracy is a separate review question with its own authorized scope.
Does this method prove AI-generated formulas are reliable?
No general reliability rate is established. The method provides bounded evidence about one formula and requirement. Broader claims require a suitable evaluation beyond a small diagnostic fixture.
Recommended books
Paul McFedries's Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365), published by Microsoft Press, provides context for understanding worksheet formulas. Use current official references for availability and exact function behavior rather than extending a book's edition to every installed version.
Steve McConnell's Code Complete, second edition, offers broader software-construction context for making assumptions visible and reviewing logic. Its relevance here is disciplined reasoning about a small computation, not spreadsheet-specific documentation or empirical evidence about AI formula accuracy.
Don Norman's The Design of Everyday Things, revised and expanded edition, helps explain why plausible feedback can be mistaken for successful intent. A returned label is visible feedback; comparing it with the intended relation remains a separate responsibility. The book does not establish a particular function's matching rules.
Further exploration
Find a lookup whose missing outcome is currently hard to distinguish from valid data. Write an absent synthetic key and its expected result before inspecting the formula. Then trace match mode, return field and presentation handling until the actual behavior is explainable.
For a later review, compare the same formula's assumptions with a source-table update. New duplicate keys or shifted ranges can invalidate an earlier acceptance even if the formula text is unchanged. Keep the requirement and fixtures close enough to the workbook that this change is recognizable rather than silently trusted.
Primary evidence sources
Related concepts: systems thinking, wealth structure, and the survival loop. Read the editorial policy and citation guide.