Exploring database functions
There are twelve database functions in Excel that cover most of the clusters of functions. These functions have been in the Excel library for many years, and are often forgotten about or not known, as there are so many other functions we can use today.
Database functions are a substitute for IFS functions (explained in a previous topic) and are definitely more powerful in application.
There is one action you need to take prior to constructing a formula using database functions: Make sure that your dataset is formatted as a tabular table. Each column in the dataset needs to have a column header.
Learning database function syntax
The syntax for database functions is really simple and is the same for each type of database function you construct. We will look at a few database functions in this topic, so let’s
look at the DGET database function as an example. The DGET function returns only one matching record. If it locates more than one match, the argument result will return
#NUM! error. This function is very similar to the XLOOKUP or VLOOKUP functions. Let’s dissect the syntax.
=DGET(database,field,criteria)
Table 10.5 – DGET syntax explanation
Now that we are confident about constructing the database functions, having understood the syntax requirements, we can look at a few database function examples.
Using DGET to return a single value
- Open the worksheet named Database.xlsx.
-
Notice that the database is formatted as a data table. Make sure that this is the case as we would need the function to update based on any new data being added to the table. The function will be built in cell E3 as this is where we would like to see the formula result returned. In cell D3, we have entered a product named Lighting
– halogen. The contents of this field can be used to locate the retail cost in the database for any product that is entered manually into cell D3.
- Type =DGET( to start the formula.
- Select the database range, in this case, A5:G25. The name of the data table should automatically populate instead of the cell reference at this point. The database table is named Inventory in this workbook, hence the name is now visible in the formula. Ensure that you do have the column headers included in the selection. This is designated by [#All] directly after the table name (refer to the following screenshot example). Insert a comma to move to the next argument.
Figure 10.26 – Formulating the DGET database function
- The next argument we will enter is the field. There are two methods for inserting the field:
-
Identify the column number. In the case of the example, we would insert 6, as
Retail is the sixth column in the worksheet.
-
Include the column header name in inverted commas. Here, we would insert
“Retail”.
-
- Once you have decided which method to use, add another comma to move to the final argument.
- Lastly, we need to select the criteria range. This is the field header and the cell directly beneath it. In this example, we will select cells D2:D3. Make sure D2:D3 is made absolute.
- Press Enter to see the result. Now, change the product to another type to see the retail cost for a different product.
- Should you wish to make the worksheet more efficient, create a validation rule to list the products in cell D3 and then use the drop-down list to choose a product instead.
Figure 10.27 – Creating a Product validation rule
Let’s look at a few more examples of database functions.
