DGET function
PRO
BASIC
The DGET
function in Jspreadsheet Formulas Pro is a handy tool that allows you to pull out a specific value from a database-like array based on certain criteria you set. Imagine having a large table of data and you only need one particular piece of information from it. By using the DGET
function, you can easily specify the conditions and retrieve that single value without having to sift through the entire table.
Documentation
Extracts a single value from a database table-like array based on specified criteria.
Category
Database
Syntax
DGET(database, field, criteria)
Parameter | Description |
---|---|
database |
The database range to use for the lookup. Should include headers for each column. |
field |
The column header of the field you want to extract the value from. |
criteria |
Criteria range or array to search for records that meet all of the specified conditions. |
Behavior
DGET
function is used to extract a single value from a database/list based on given conditions. It has the following behaviors:
- The function expects three arguments - database range, field, and criteria range. If any of these are missing or incorrectly defined, it will return an error.
- The function is case-insensitive, meaning it does not differentiate between uppercase and lowercase letters.
- When the field argument is a text value, the
DGET
function matches it with the column label in the database. If it is a number, the function considers it as the column index number. - The
DGET
function ignores empty cells in the database. - It can handle both text and numbers in the database. However, the field and criteria should match with the data type in the database. For instance, if the database contains numbers, the criteria should not be text and vice versa.
- The function does not process boolean values directly. They need to be converted into their numeric equivalents (TRUE to 1 and FALSE to 0) before processing.
- If the criteria match multiple records, the function returns an error. It only works when there is exactly one record that matches the criteria.
Common Errors
Error | Description |
---|---|
#VALUE! | The function returns this error if the field argument is neither a valid column label nor a valid index number. |
#NUM! | This error is returned when there are no records matching the criteria or when there are multiple records that match the criteria. |
#REF! | This error occurs when the given database range or criteria range is invalid. |
#N/A | The function returns this error if the database or the criteria is empty. |
Best practices
- Always ensure that the field and criteria match the data type in the database. If the database contains numbers, avoid using text as criteria and vice versa.
- It's recommended to use unique labels for columns in the database to avoid confusion.
- Be careful while defining the criteria. Remember,
DGET
function returns an error if there are multiple records that match the criteria.- For efficiency and readability, avoid using entire column references like A:A as the database range. Instead, use specific ranges like A1:C10.
Usage
A few examples using the DGET function.
DGET(A1:C10,"Salary",[A1:A10,"John",C1:C10,">1000"]) returns the salary for John if their salary is greater than 1000
DGET(A1:C10,"Name",[A1:A10,"*doe*",B1:B10,">=3"]) returns the name of the first person whose last name contains "doe" and has been with the company for at least 3 years
DGET(A1:E10,"Phone",[A1:A10,"Jane",B1:B10,"<>Sales",C1:C10,"<>Seattle"]) returns the phone number for Jane if they're not in Sales and not located in Seattle
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTFlZDRiMTljYmE4ZGJjZDdiNTg2YjQwYjg3MmM5ODkzMzAyODc3ZGE5ZWE0NWVhODc4M2JhNmMzOTExMmUzMzc2NDU5MzUxYjk2N2FjNjRkYzE1NDc0ZDc3ZWJkMzVkNzBjZDc0ZjA3ZTRlNmEzZmNiY2ExYWYxMzQ5MGRlNTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Name",
"Department",
"Salary"
],
[
"John",
"Sales",
1200
],
[
"Jane",
"Marketing",
800
],
[
"Bob",
"Sales",
1500
],
[
"Alice",
"IT",
950
],
[
"",
"",
""
],
[
"Name",
"Salary"
],
[
"John",
">1000"
],
[
"",
""
],
[
"Result:",
"=DGET(A1:C5,\"Salary\",A7:B8)"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('OTFlZDRiMTljYmE4ZGJjZDdiNTg2YjQwYjg3MmM5ODkzMzAyODc3ZGE5ZWE0NWVhODc4M2JhNmMzOTExMmUzMzc2NDU5MzUxYjk2N2FjNjRkYzE1NDc0ZDc3ZWJkMzVkNzBjZDc0ZjA3ZTRlNmEzZmNiY2ExYWYxMzQ5MGRlNTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Name",
"Department",
"Salary"
],
[
"John",
"Sales",
1200
],
[
"Jane",
"Marketing",
800
],
[
"Bob",
"Sales",
1500
],
[
"Alice",
"IT",
950
],
[
"",
"",
""
],
[
"Name",
"Salary"
],
[
"John",
">1000"
],
[
"",
""
],
[
"Result:",
"=DGET(A1:C5,\"Salary\",A7:B8)"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('OTFlZDRiMTljYmE4ZGJjZDdiNTg2YjQwYjg3MmM5ODkzMzAyODc3ZGE5ZWE0NWVhODc4M2JhNmMzOTExMmUzMzc2NDU5MzUxYjk2N2FjNjRkYzE1NDc0ZDc3ZWJkMzVkNzBjZDc0ZjA3ZTRlNmEzZmNiY2ExYWYxMzQ5MGRlNTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Name",
"Department",
"Salary"
],
[
"John",
"Sales",
1200
],
[
"Jane",
"Marketing",
800
],
[
"Bob",
"Sales",
1500
],
[
"Alice",
"IT",
950
],
[
"",
"",
""
],
[
"Name",
"Salary"
],
[
"John",
">1000"
],
[
"",
""
],
[
"Result:",
"=DGET(A1:C5,\"Salary\",A7:B8)"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTFlZDRiMTljYmE4ZGJjZDdiNTg2YjQwYjg3MmM5ODkzMzAyODc3ZGE5ZWE0NWVhODc4M2JhNmMzOTExMmUzMzc2NDU5MzUxYjk2N2FjNjRkYzE1NDc0ZDc3ZWJkMzVkNzBjZDc0ZjA3ZTRlNmEzZmNiY2ExYWYxMzQ5MGRlNTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Name",
"Department",
"Salary"
],
[
"John",
"Sales",
1200
],
[
"Jane",
"Marketing",
800
],
[
"Bob",
"Sales",
1500
],
[
"Alice",
"IT",
950
],
[
"",
"",
""
],
[
"Name",
"Salary"
],
[
"John",
">1000"
],
[
"",
""
],
[
"Result:",
"=DGET(A1:C5,\"Salary\",A7:B8)"
]
]
}]
});
}
}