A key skill in Excel training is to be able to lookup and retrieve data from a range of records. The most popular way of achieving this is to use the VLOOKUP function. The VLOOKUP function in Excel is awesome and easy to use, but it has its drawbacks. Cue the Excel DGET function.
A very powerful Excel function that will retrieve data from a record without the limitations of VLOOKUP. Advantages of using the DGET function include:
- It can retrieve data from a column to the left of the column you searched within.
- It can lookup data based on multiple conditions.
- It can handle both AND and OR logic.
The Formula
The Excel DGET function is a database function, a group of very powerful functions for analysing large tables of data.
The DGET function is written as below;
=DGET(database, field, criteria)
| Database | The range of cells where you want to search for and retrieve the data. The first row must contains the headings for each column. |
| Field | The column containing the information that you want to return. This can be entered as the column’s index number i.e. 5, or you can use the column heading enclosed with quotation marks e.g. “Salesperson”. |
| Criteria | The range of cells that contain the conditions for your search. The first row must contain the column heading. |
Excel DGET Function Example
The following formula has been entered into cell J3 in the image below it.
It looks within range A1:G800 and returns whatever data is finds in the first column. The criteria for the search is stored in range I2:I3. I2 contains the column heading of Order ID. This exactly matches the one found in row 1 of the database. Cell I2 contains the content to search for, in this case 11248.
=DGET($A$1:$G$800,1,$I$2:$I$3)
Did you receive an error message?
The #NUM error message is displayed if the DGET function finds more than one record that meets the search criteria.
The #Value error message is shown if no records were found matching the search criteria.
The DGET function in Excel is not case sensitive.
Thanks