Excel Training - Lookup your Data with the DGET Function

1 Flares Filament.io 1 Flares ×

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 on Excel is awesome and easy to use, but it has its drawbacks.

Cue the 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 DGET function is a database function, a group of very powerful functions for analysing large tables of data. (More Excel training – See more on Database functions in Excel).

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.

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)