DGET Function - Lookup data in excel with multiple criteria

Retrieving data from a table/range without any limitations is a dream for almost every excel user. VLOOKUP is a very powerful function but unfortunately it doesn't allow us to lookup values in the left columns. Further more, with VLOOKUP we can only retrieve data considering one criteria...which might not be enough in some cases.

There is of course the possibility of combining functions to overcome situations mentioned above but for all those who prefer to use simple formulas,  Excel provides the DGET function.

How to use the DGET function:
=DGET(database, field, criteria)

Here are a couple of examples:

Example #1 - Two search criterias(Item and Rep) that will retrieve a value from a column on the RIGHT.
There is only one row where Jones has sold pencils (row #3). The formula used for this example will return a value from column number 5 (Column E), as requested in the second parameter of our formula (field)

Example #2 - Three criterias (Rep, Item and Units) that will retrieve a value from a column on the LEFT.
In this example, there is also only one row on the table where Jones as sold 95 Pencils. The formula used in this example will return a value from row #3 and column number 2 (Column B), as requested in the formula.

Did you receive an error message?
This function can return one of the following two errors:
#NUM! - This message is displayed if the DGET function finds more than one record that meets the search criteria.

#VALUE! - This message is shown in case no records were found matching the search criteria.

PROS:
 - Retrieve data from a column to the right or left;
 - Lookup data based on multiple conditions;
 - Warning about multiple records with the same search critera.

CONS:
 - Inability of using auto fill the formula to several rows.


By the way, DGET function is also NOT case sensitive.

Video example from YouTube: