How to Use the DGET Function in an Excel Database

By Ken Bluttman

DGET is a unique Excel database function. It does not perform a calculation but checks for duplicate entries. The function returns one of three values:

  • If one record matches the criterion, DGET returns the criterion.

  • If no records match the criterion, DGET returns the #VALUE! error.

  • If more than one record matches the criterion, DGET returns the #NUM! error.

By testing to see whether DGET returns an error, you can discover problems with your data. Perhaps you suspect that a student has registered twice for a specific class. If this is true, two records will have the same Student ID and Class.

The following figure shows how to check whether student NR5090 is entered more than once for Calculus 101. If there is more than one record, DGET returns an error. Cell F5 contains a formula that nests the DGET function inside the ISERROR function; all that is inside the IF function. If DGET returns an error, return one message; if DGET does not return an error, return a different message. Here is the formula:

Using DGET to test for duplicate records in a database.

Using DGET to test for duplicate records in a database.
=IF(ISERROR(DGET(Students,"Student ID",F2:G3)),F3 & " has duplicate records", F3 & " has one record")