PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
Contents
EXCEL DATABASE FUNCTIONS-DGET
INTRODUCTION
EXCEL provides a complete set of functions to deal with the database data.
DATABASE data has the first row as headers.
DGET FUNCTION RETURNS THE VALUE FROM THE DATABASE DATA WHICH SATISFIES THE CONDITION. IT RETURNS THE VALUE ONLY IN THE CASE, IF THE RETURNING VALUE IS UNIQUE.
DGET FUNCTION is very simple and easy to use when we want to do a simple Lookup job in a database.
Let us find out the purpose, syntax and examples of DGET functions.
PURPOSE OF DGET FUNCTION IN EXCEL
DGET FUNCTION RETURNS THE VALUE THE VALUE WHICH SATISFY THE GIVEN CONDITION.
DGET function is helpful in a situation where we need to lookup a single value against a given value which satisfies the condition.
For example,
If we want to know the name of the candidates against a particular IDENTITIY NUMBER , we can make use of this function to get that particular name and other details in an easy manner.
We’ll demonstrate this with the help of examples in the next section.
PREREQUISITES TO LEARN DGET
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- If you already know how to use the database functions, it’ll be beneficial and make the learning of DGET function much easier.
- Of course, Excel software.
SYNTAX: DGET FUNCTION
The Syntax for the DGET function is
=DGET(database or range, column or field, Criteria )
The DGET function syntax has the following arguments:
Database Database is the data on which we want to use the DGET function. The data is specifically arranged in DATABASE STYLE with LABELS in the top row and data in the lower rows. The range mentioning the database is given including the cells containing the Labels. Following picture shows the sample of database.
Field Column or field is the column on which the criteria will be applied. The column is given by the column name in the “” or it can be given as the column number of the database such as 1 ,2 and so on.
Criteria It is the range which contains the criteria or conditions. It is important that the range contains the column label. It’ll become clearer with the example discussed below.
THE CRITERIA FOR THE RESULT WILL BE GIVEN AS A RANGE ONLY CONTAINING THE COLUMNS WHICH CONTAIN THE CONDITIONS TO BE FULFILLED.
THERE CAN BE ANY NUMBER OF COLUMNS BUT ATLEAST ONE COLUMN WITH ONE CELL IS NEEDED ELSE THE FUNCTION WILL RETURN AN ERROR.
EXAMPLE:DGET FUNCTION IN EXCEL
DATA SAMPLE
DGET FUNCTION can be used easily once you understand the way how it is used .
For the ease of understanding let us take an example of information regarding the employees of a company.
The table below shows the data present with us . Let us try to find out the following solutions using the DGET function in Excel.
| IDENTITY NUMBER | EMPLOYEE NAME | LOCATION | AGE | EXPERIENCE |
| 101121 | DAVE | US | 32 | 7 |
| 101125 | JACK | UK | 33 | 8 |
| 101187 | JJ | UAE | 36 | 11 |
| 101198 | JOE | FRANCE | 34 | 9 |
| 101174 | RHODES | INDIA | 31 | 6 |
| 101169 | DANIEL | SINGAPORE | 38 | 13 |
| 101195 | ROSS | BRAZIL | 40 | 15 |
1. Find out the employee name with identity number 101198.
2. Find out the location of the employee named DANIEL.
1. FINDING OUT THE EMPLOYEE NAME WITH IDENTITY NUMBER 101198
- After we have our data ready i.e. The table must be having the column labels at the top. [ There is no specific requirements for the column labels. The function would consider the first row as column labels. ]
- The criteria needs to be declared as a range. The range must contain at least one column label at the top followed by the condition under the columns.
- Put the formula in a cell where you want the result =DGET(D5:H12,”EMPLOYEE NAME”,D17:F18)
- The function returns the result as ROSS.
- The following animated picture shows the process of using DGET FUNCTION to demonstrate the example discussed.
EXPLANATION: FINDING OUT THE EMPLOYEE NAME WITH IDENTITY NUMBER 101198
The function used for the solution is =DGET(D5:H12,”EMPLOYEE NAME”,D17:F18)
Let us analyze the formula used.
The first parameter i.e. D5:H12 is the complete range of the Database [data ].
The second parameter “EMPLOYEE NAME” is the field or column on which the condition would work and result will be returned. As we want to know the EMPLOYEE NAME , this column is needed.
The column is always given in the Double Quotes. [” “].
The last parameter is the range containing the Criteria D17:F18.
Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.
In this case , we need to find out the name of the employee so we mentioned the IDENTITY NUMBER in the criteria.
The RESULT comes out to be JOE which is correct.
*Although we have taken three columns in this criteria. It’ll work equally good even if we just declare ONE COLUMN with ONE CONDITION.
2. FIND OUT THE LOCATION OF THE EMPLOYEE NAMED DANIEL.
- After we have our data ready i.e. The table must be having the column labels at the top. [ There is no specific requirements for the column labels. The function would consider the first row as column labels. ]
- The criteria needs to be declared as a range. The range must contain at least one column label at the top followed by the condition under the columns.
- Put the formula in a cell where you want the result =DGET(D5:H12,”EMPLOYEE NAME”,D17:F18)
- The function returns the result as ROSS.
- The following animated picture shows the process of using DGET FUNCTION to demonstrate the example discussed.
EXPLANATION: FIND OUT THE LOCATION OF THE EMPLOYEE NAMED DANIEL.
The function used for the solution is =DGET(D5:H12,”EMPLOYEE NAME”,D17:F18)
Let us analyze the formula used.
The first parameter i.e. D5:H12 is the complete range of the Database [data ].
The second parameter “LOCATION” is the field or column on which the condition would work and result will be returned. As we want to know the LOCATION , this column is needed.
The column is always given in the Double Quotes. [” “].
The last parameter is the range containing the Criteria D17:F18.
Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.
In this case , we need to find out the LOCATION of the employee named DANIEL.
The RESULT comes out to be SINGAPORE which is correct.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.