Lookup & Reference
Strict Single-Match Lookup (DGET Uniqueness Guard vs. Silent XLOOKUP First-Match)
Enforce primary-key uniqueness in lookup formulas by reproducing DGET's exact single-record behavior—returning the value when 1 row matches, NO_MATCH when 0 match, and DUPLICATE_KEY when 2+ rows collide.
Why XLOOKUP and VLOOKUP silently hide duplicate master-data keys
Standard lookup functions (VLOOKUP, XLOOKUP, INDEX/MATCH) stop at the first matching row. If an employee ID, SKU, or vendor code accidentally appears twice with two different rates, first-match lookups silently return the top row while ignoring the conflicting duplicate below.
Excel's database function DGET was valued by auditors specifically because it raises #NUM! whenever more than one record satisfies the criteria—a contract you can implement cleanly without rigid criteria-header ranges by pairing COUNTIFS with XLOOKUP or FILTER.
| Matching Rows (n) | XLOOKUP Behavior | DGET Guard Behavior | Audit Action |
|---|---|---|---|
| n = 0 | Returns if_not_found | "NO_MATCH" | Flag missing master record |
| n = 1 | Returns single match | Returns exact value | Clean primary-key lookup |
| n >= 2 | Silently returns 1st row | "DUPLICATE_KEY" | Halt & deduplicate master table |
Cell-Referenced DGET Uniqueness Guard Formula
Count matching keys first with COUNTIF or COUNTIFS; return the value only when hit_count = 1.
=LET(hit_count, COUNTIF($A$2:$A$6, E2), IF(hit_count = 0, "NO_MATCH", IF(hit_count > 1, "DUPLICATE_KEY", XLOOKUP(E2, $A$2:$A$6, $C$2:$C$6))))Collaborative Duplicate Resolution in Arte Sheets
When a shared pricing or HR rate table in Arte Sheets surfaces "DUPLICATE_KEY", reviewers can leave a threaded cell comment with an @mention to notify the data owner, or contributors with Suggest-only permissions can submit a cell proposal correcting the duplicate ID before downstream payroll or invoice sheets finalize.
=COUNTIF(D2:D6, ">1")Try the example
Use the original values or change them to test your understanding.