Confusing excel formulae DGET or Vlookup? Which one to use and when?

DGET formula helps to extract the record based on the conditions specified. Now, it works similar to VLOOKUP formula but in some cases it is preferable. But before we dive in those cases, let us first see how DGET formula works.

DGET (database, field, criteria) whereas:

database is the range of cells from which you want to extract data based on the criteria specified

field represents the column header name or serial no. from which you want to extract data

criteria is the range of cells that contains the conditions specified. Please note, the cell range should include the header value and one cell below the header.

Let us understand this with an example:

Consider a dataset consisting of 3 columns viz. Sales, Month (1-12) and Code No. Based on the code no., we want to find the respective sales.

Now, if you have used Vlookup before, the first step would be to put column C before column A (i.e. Code No., Sales, Month - in that order) because Vlookup always work from Left to Right direction (i.e. the column from which you intend to find the value should be on RHS to the criteria column). But, DGET formula can work both ways.

Let us compare both the formulae here, so that you get a better understanding.

The agenda here is to find the respective sales for Code No.5

First we will use Vlookup to find the sales.

=vlookup(C3,A1:C10,1,False)

Notice that Vlookup returns #NA because Sales column is on LHS to the Code No. column. But for Vlookup to work properly, Sales column should be on RHS to Code No. Column.

But, when we rearrange the columns as shown here, Vlookup works properly.

=VLOOKUP(E3,E1:G10,2,false)

Now, Let us try to solve this using DGET formula (without rearranging the columns)

There's a small catch here, for DGET function to work properly, we need to put criteria column label and value in separate cells (in this case, C12:C13). Also, if you put the criteria cells in horizontal position (i.e. C12:D12), it won't work.

Now, using formula = dget(A1:C10,A1,C12:C13), we get the result in C17 cell.

Note that, there are some advantages as well as limitations to use DGET function.

Advantages:

• It does not depend on the columns arrangement of dataset
• It works well in case you have drop down values (Data Validation) in the criteria cell (C13 in the above example)

Limitation:

• If there are duplication values in he criteria column (column C in our example), the function throws an error. Hence the criteria column should contain unique values

So, next time when you find such a case, you can use your best judgement on whether to use DGET or Vlookup.

Thanks! Keep Learning!

P.S. If you find this article useful, do like, comment & share!