You might recall in an earlier free Excel help article we wrote where we discussed about the Microsoft Excel VLOOKUP function how we explained fetching data from one worksheet into another. In this article we’ll look at another function that fetches data, the DGET function. The DGET function is one of the several database functions in Excel 2010. So unlike the VLOOKUP you must define field headers and also include them in your database. You might be wondering how a function in Excel is being called a database function when Excel is only a spreadsheet. We’ll see that in a moment.
As the name suggests DGET or Database Get fetches data from one Excel worksheet into another. However this is just a simple definition of the function. DGET can do more complex searches based on criteria we provide. Let us study DGET in more detail.
Let us say we need to fetch individual records containing one or more fields according to criteria we specify. For example we enter a salesperson’s name and we need to fetch all details i.e Sales, Grade, State, Month details of that salesperson’s record. Suppose we also need to find out a salesperson (and the record) for a particular criteria, for both the above scenarios DGET is the appropriate function we need to use. We’ll see in a moment how this can be accomplished using DGET.
Like all other database functions, to use DGET, there are certain pre-requisites. Do you recall just a while ago we mentioned that DGET is a database function? So where is our database? Ok, here goes. We actually don’t use a Database but we present the data inside the spreadsheet in the form of a database. Didn’t get it? Read on. So what does a database typically have? A database has fields and records. A record is a group of fields. So all we need to do in Excel is to present this data in the form of fields and records. Many of you have actually been doing this ever since you used Excel but were unaware of it. Remember your columns and column headers in your spread sheet. These become the fields. A row in your spreadsheet becomes the record. But to use these columns and rows with DGET we’ll have to remember some fundamental and mandatory rules without which DGET will not function properly.
- Your columns must have headers.
- No empty or blank cells are allowed. So all cells MUST have data in them. ABSOLUTELY no blank rows either.
- There must not be data in cells adjacent i.e. touching the cells we want to include in the database.
The syntax of the DGET function is as follows – DGET(database, field, criteria). Let’s explore the parameters one by one.
Database – This is what we have been discussing so far. Let us assume we have the following fields i.e. column headers Salesperson, State, Month(Date), Sales and Grade
Field – Is the field or column where we want to count the value, in our case the Grade field to count all the “A” grades.
Criteria – Now here is where you can add other conditions, one or more. To specify criteria we have to do some additional steps. In an unused portion of your spreadsheet create an identical table, this time with two (for clarity) row cells Field Name and Criteria, exactly one on top of the other. This is a mandatory condition. So the row of headers will contain the following headers, Field Name, Salesperson, State, Month(Date) Sales and Grade. You will fill in the cell just below Field Name with the word Criteria. This table must not be touching any cells of the main database table.
We’ll now look at some examples of DGET computations using criteria.
First let us name the database here so that we may just have to refer to it by name instead of selecting it each time. To name the database select all the fields and all the rows (Ctrl+down arrow). You can also use the keyboard shortcut Ctrl+*. Next name the selection by entering name in the Name field (top left) and this becomes the name of our database. Let’s say our database is named, Sales1.
So at the cell where we want to add the DGET function enter =DGET(Sales1,
Next, click on the cell header of the column where you are going to fetch a record’s details that matches our criteria i.e. for a particular salesperson. In our example we click on the Sales column header as we want to fetch the sales for that salesperson. The cell is automatically inserted into formula. Alternatively you can also enter the name of the column header as “Sales” or double click or use F9.
Next we need to input the criteria. For criteria we need to click on the field we wish to use in the criteria. In our example we wish to use the Salesperson’s name and so we enter “XXXXXX” (where XXXXXX denotes the name of a salesperson) in the cell below the header cell that is titled Salesperson in the separate data table we created for field and criteria. Now click the field Salesperson and drag in the value for the field for criteria also from the Criteria row where we had entered the salesperson’s name.
The DGET function will now have the criteria data embedded inside the formula and the sales details of the salesperson will be displayed in the cell.
The final formula will look something like this – =DGET(Sales1, “Sales”, I14:I15)
Note that since we need to get all the other fields of a salesperson’s record i.e Sales, State, Month(Date), Grade we need to copy the formula to other cells using the exact field we are looking for.
Therefore additional DGETs for the rest of the fields would be:
DGET(Sales1, “Sate”, I15:I15) for State, DGET(Sales1, “Month(Date)”, I16:I15) for Date, DGET(Sales1, “Grade”, I17:I15) for Grade and so on.
Note that as we discussed earlier you can have more than one criterion. Suppose we have multiple records for salespersons in a state (one for each month but for several months) and you want to fetch details of sales for a particular month for example for the month on May. Under Month(Date) in the criteria table enter May. This time when you click and drag cells for criteria, drag in the Salesperson field and the name of the salesperson and the Month(Date) and “May” criteria too. This was a “AND” condition i.e. one where we added two conditions. Now suppose you wanted an “OR” condition place the second condition below the first condition, i.e. vertically and not horizontally, and then drag them both.
The results, the details of the Sales, Grade, State etc., for the salesperson for May are fetched in the cells.
Note that there is yet another method of inserting the DGET function into the spreadsheet. For this first select the cell where you would like to insert the function. Then from the Formula tab, the Function Library group, choose Insert Function. The Insert Function dialog opens. Here select the DGET function from the Select a Function box. Note that you need to ensure you have selected the Database category located just above the Select a Function box. A new Function Arguments dialog is displayed where you can select the Database, Field and Criteria by clicking the buttons (located adjacent to the corresponding fields) which will take the control back to your spreadsheet and back to the dialog by inserting the selected ranges into the fields. When done click OK and your function is all set. As a confirmation your cell will now have the value computed by DGET inside.
Yet another usage of the DGET function is explained in an upcoming free Excel help article for all of you experienced Excel Consultant to read.