Stockertim Dictionary · Technology
Exact match
Also called exact matching, exact match lookup, FALSE match type
In short
A lookup setting that returns a result only when the lookup value is found exactly, and an error or a chosen message when it is not found.
- Beginner
- Concept
- Checked
In detail
Lookup functions can search in two ways, and exact match is the one needed for codes, names and identifiers. The function looks for an entry identical to the lookup value, ignoring case but nothing else. If it finds one it returns the matching item; if not, it returns #N/A instead of guessing.
Each function asks for it differently. XLOOKUP uses exact match by default and has a fourth argument for the text to show when nothing is found, as in =XLOOKUP(A2,Codes,Prices,"Not found"). VLOOKUP defaults to approximate match, so exact matching requires FALSE or 0 as the fourth argument: =VLOOKUP(A2,Prices,2,FALSE). MATCH also defaults to an approximate mode and needs 0 as its third argument. Forgetting the extra argument in VLOOKUP or MATCH is one of the most common spreadsheet errors.
The #N/A result is useful information rather than a nuisance. It shows that a code in one list is missing from the other, which may point to a new product, a typing error or a record that was never set up. Wrapping every lookup in IFERROR to hide these errors removes that signal; IFNA is narrower because it catches only the not-found case and leaves genuine formula faults visible.
Exact match does not require the list to be sorted, and it returns the first match when there are several. With large tables it is slower than approximate matching on sorted data, though for most office sheets the difference is not noticeable.
Worked example
A garage in Bristol looks up part code BR-220 in a list that holds BR-200, BR-210 and BR-230.
- Exact match: no entry equals BR-220, so the result is
#N/A, or the text"Not found"with XLOOKUP's fourth argument - Approximate match on the same codes would quietly return the row for BR-210
Only exact match exposes that the part code is missing from the list.
In practice
Reconciliations, price lookups, payroll and any work that joins two lists by an identifier use exact match. Many teams treat an approximate-mode VLOOKUP on codes as a defect when reviewing a workbook.
Watch out
- VLOOKUP and MATCH need an explicit exact-match argument; XLOOKUP does not.
- Hiding
#N/Awith IFERROR conceals missing records as well as errors.
Learn it properly
Official sources
- The AQuA Book: guidance on producing quality analysis for government · HM Treasury
- The Government Data Quality Framework · UK Government Data Quality Hub
Facts last checked on 9 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.