Adding the OR branches
A record may satisfy both criteria rows. The selected database record contributes once, not once per branch.
Retrieve exactly one database record
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter a formula in N5 that returns the field selected by H6 from A1:E13 when exactly one record matches H1:K3. Use AND within a criteria row and OR between rows; a record matching both rows is still one match. Return genuine #VALUE! for no matching record and genuine #NUM! for multiple matches. Do not return the first match, hide cardinality errors, or turn an identical literal text value into an error. A unique numeric zero or Boolean FALSE remains a valid result.
Read and edit the applied criteria
All nonblank conditions on one row use AND; the two rows use OR. A blank row matches every record. These controls store literal text, so =East stays a criterion rather than becoming an executable formula.
| Criteria row | Region | Status | Units rule 1 | Units rule 2 |
|---|---|---|---|---|
| 2 | =West | =Released | >=3 | <=3 |
| 3 | =West | =Released | >=3 | <=3 |
Applied field:
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: N5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Separate zero, one and multiple matching records while preserving the selected field type.
THE QUICK ANSWER01
Enter a formula in N5 that returns the field selected by H6 from A1:E13 when exactly one record matches H1:K3. Use AND within a criteria row and OR between rows; a record matching both rows is still one match. Return genuine #VALUE! for no matching record and genuine #NUM! for multiple matches. Do not return the first match, hide cardinality errors, or turn an identical literal text value into an error. A unique numeric zero or Boolean FALSE remains a valid result.
ƒx
=DGET($A$1:$E$13,$H$6,$H$1:$K$3)For the example data
N5 0
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Criteria are data, not query fragments
Region and Status criteria use text prefixes, wildcard patterns or explicit =exact text. Comparisons are case-insensitive; * matches multiple characters, ? matches one, and ~ escapes a wildcard or tilde. A leading apostrophe is an Excel entry aid for storing a criterion beginning with = as literal text; it is not part of the stored criterion.
Keep records and numbers distinct
A criteria rule selects records first. Numeric summary functions then ignore nonnumeric values in the selected field. A matching record containing text "18" or TRUE is different from a numeric observation, and a unique literal "#NUM!" is different from the actual multiple-match error.
Keep the supported boundary visible
Units inputs are whole numbers within ±10,000. Amount accepts numeric cents within ±1,000,000, literal text, Boolean values or blanks. Numeric comparisons in this course apply only to Units. Text ordering/not-equal criteria, calculated criteria columns, external databases and typed source errors require separate rules.
Read the full step-by-step walkthrough
- Include the database headers
The first row supplies field names. The database range includes all twelve original records, not only the visible matches.
- Read each criteria row as a rule
Across H:K, all nonblank conditions on a row must hold. Across rows 2 and 3, either complete rule can select a record. Matching both rows does not create a second observation.
- Constrain the same Units field twice
The repeated Units headings apply a lower and an upper bound to one record. A completely blank criteria row matches all records.
- Select the result field
H6 is the field header Amount or Units. Keep it as a worksheet reference so the same formulas work when the selected field changes.
- Return the DGET result
Count matching records before returning a value: zero matches is #VALUE!, multiple matches is #NUM!, exactly one returns its actual value.
Things to watch
Treating both rows as AND
All columns on a row combine with AND. Different rows are alternatives.
Returning the first matching record
DGET is an exactly-one-record operation. Returning a first match conceals ambiguity.
About this example
Original criteria records and independent selection/type/cardinality rules checked against the real browser formula engine. Native Excel and Sheets recalculation/edit/save remain separate verification steps.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.