For the single row (database record) that matches the specified search criteria, DGET returns the contents of the cell (field) of the specified column.
DGET(Database; DatabaseField; SearchCriteria)
Returns an alpha-numeric value from the Database for the given DatabaseField that qualifies the SearchCriteria.
Database specifies the range of cells occupied by the database table. The first row of the range contains the field names, and subsequent rows are records with corresponding field values.
One way of defining the range of cells is to enter the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference. An example might be A1:E10.
Calc provides two methods for assigning names to cell ranges and these are known as named ranges and database ranges. The Database argument may also be specified by passing the name of a defined named range or database range. Using a meaningful name to define the cell range can enhance formula readability and document maintenance.
DatabaseField specifies the column which the function will use for its calculations after the search criteria have been applied and the data rows have been selected. It is not related to the search criteria.
Specify the DatabaseField argument in any of the following ways:
• By entering a reference to a header cell within the Database area. Alternatively, if the cell has been given a meaningful name as a named range or database range, enter that name. If the name does not match the name of a defined range, Calc reports a #NAME? error. If the name is valid but does not correspond to one cell only, Calc reports Err:504 (error in parameter list).
• By entering a number to specify the column within the Database area, starting with 1. For example, if a Database occupied the cell range D6:H123, then enter 3 to indicate the header cell at F6. Calc expects an integer value that lies between 1 and the number of columns defined within Database and ignores any digits after a decimal point. If the value is less than 1, Calc reports Err:504 (error in parameter list). If the value is greater than the number of columns in Database, Calc reports a #VALUE! error.
• By entering the literal column header name from the first row of the Database range, placing quotation marks around the header name. For example, “Distance to School”. If the string does not match one of the Database area’s column headings, Calc reports Err:504 (error in parameter list). You can also provide a reference to an arbitrary cell (not within the Database and SearchCriteria areas) that contains the required string.
SearchCriteria specifies the range of cells containing search criteria. Like Database, its first row is also field names, and subsequent rows are conditions for related fields. The Database and SearchCriteria areas need not be adjacent, or even on the same sheet. One way of defining the range of cells is to enter the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference. For example, A13:B14. The cell range may also be specified by passing the name of a defined named range or database range. The contents of the SearchCritera area are described in more detail in the Additional Details section below.
- See also the Wiki page about Conditional Counting and Summation.
- For the Database, if the name does not match the name of a defined range, Calc reports a #NAME? error. Other errors that might be reported as a result of an invalid Database argument are #VALUE! and Err:504 (error in parameter list).
- For the SearchCriteria, if the name does not match the name of a defined range, Calc reports a #NAME? error. Err:504 (error in parameter list) may also be reported as a result of an invalid SearchCriteria argument.
- In case of an error, the function returns either #VALUE! for no row found, or Err502 for more than one cells found.
- The Database category may be confused with a database integrated into LibreOffice. However, there is no connection between a database in LibreOffice and the Database category in LibreOffice Calc.
Fetching data from a data source to create a Database range
- To fetch data from a data source into the spreadsheet, open the Data Source Explorer with menu View > Data Sources (Ctrl + Shift + F4), select the data source in the left pane and open the tree to display the tables or queries. Click on the table or query to display it in the right pane of the explorer.
- Click on the top left corner of the data displayed in the Explorer then drag and drop to the top left corner of the target cell in the spreadsheet. A new database range is created with the default name of import1.
- Choose Data > Define Range to open the Define database range dialog and set the database range properties.
Defining Search Criteria
- The number of columns occupied by the SearchCriteria area need not be the same as the width of the Database area. All headings that appear in the first row of SearchCriteria must be identical to headings in the first row of Database. However, not all headings in Database need appear in the first row of SearchCriteria, while a heading in Database can appear multiple times in the first row of SearchCriteria.
- Search criteria are entered into the cells of the second and subsequent rows of the SearchCritera area, below the row containing headings. Blank cells within the SearchCritera area are ignored.
- Create criteria in the cells of the SearchCritera area using the comparison operators <, <=, =, <>, >=, and >. = is assumed if a cell is not empty but does not start with a comparison operator.
- If you write several criteria in one row, they are connected by AND. If you write several criteria in different rows, they are connected by OR.
- Criteria can be created using wildcards, providing that wildcards have been enabled via the Enable wildcards in formulas option on the Tools > Options > LibreOffice Calc > Calculate dialog. When interoperability with Microsoft Excel is important for your spreadsheet, this option should be enabled.
- Even more powerful criteria can be created using regular expressions, providing that regular expressions have been enabled via the Enable regular expressions in formulas option on the Tools > Options > LibreOffice Calc > Calculate dialog.
- The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools > Options > LibreOffice Calc > Calculate.
- One final setting that affects how the search criteria are handled is the Search criteria = and <> must apply to whole cells option on the Tools > Options > LibreOffice Calc > Calculate dialog. This option controls whether the search criteria you set for the Database functions must match the whole cell exactly. When interoperability with Microsoft Excel is important for your spreadsheet, this option should be enabled.
The following data will be used in some of the function description examples:
The range A1:E10 lists the children invited to Joe's birthday party. The following information is given for each entry: column A shows the name, B the grade, then age in years, distance to school in meters and weight in kilograms.
|1||Name||Grade||Age||Distance to School||Weight|
|13||Name||Grade||Age||Distance to School||Weight|
The formula in cell B16 is =DCOUNT(A1:E10;D1;A13:E14)
|=DGET(A1:E10;"Grade";A13:E14) where we enter the name Frank in A14 and delete everything else from 14th row.||Frank is in second grade. We want to determine what grade a child is in, whose name was entered in cell A14.||2|
|=DGET(A1:E10;"Age";A13:E14) where we enter the name Frank in A14 and delete everything else from 14th row.||Frank is seven years old. We want to determine what is the age of the child, whose name was entered in cell A14.||7|
|=DGET(A1:E10;"Name";A13:E14) where we enter 11 in C14 and delete everything else from 14th row.||Frank is in second grade. We want to determine what is the name of the child, whose is 11 years old.||Daniel|
|=DGET(A1:E10;"Name";A13:E14) where we enter 9 in C14 and delete everything else from 14th row.||More than one children have age 9 so the function returns an error value||#Value!|
|=DGET(A1:E10;"Name";A13:E14) where we enter 11 in C14 and we enter <600 in D14 cell||No row qualifies this SearchCriteria so the function returns an error value.||Err:502|
For more detailed examples, download and view this Calc spreadsheet.
Related LibreOffice functions:
Equivalent Excel functions: