DGET vs VLOOKUP in Excel (7 Differences)

Microsoft Excel provides efficient functions that allow one to search for and look up data. We will compare Excel DGET vs. VLOOKUP functions of the lookup functions. The DGET function is not known to all. It is a database function and very useful. So, let’s start with the main article to understand the 7 distinct differences between these functions.

DGET vs VLOOKUP Functions in Excel: 7 Key Differences

The United States Tennis Association has a ranking system, called the National Tennis Ratings Program (NTRP) which designates NTRP ratings based on the tennis player’s skill level. For the Adult NTRP Ratings, the skill level is designated by looking at the results of the player at hand, in USTA League and/or USTA NTRP adult tournaments. The scale starts at 2 and increases in increments of 0.5 all the way up to 7.
A hypothetical tennis coach is interested in retrieving the USTA rankings of certain players at his clinics, in order to pitch equally ranked players against each other in future matches. The source data is shown in the following images.

1. Syntax of DGET vs VLOOKUP in Excel

The syntax of the DGET function and the VLOOKUP function are different from each other. In this section, we will show the differences between these functions.

1.1. DGET Function

The DGET function has the following three compulsory arguments by using which it will return the value.

where the database is the entire data set and the first row contains the labels or column headings, the field indicates the column that is used in the function, and criteria are the group of cells that contains the conditions that one wants to specify.

1.2. VLOOKUP Function

In the VLOOKUP function, we have the following three mandatory arguments with an optional argument.

Where lookup_value is the value you want the function to use in order to find the matching output value, table_array is the range that contains all the columns of interest, the col_index_num specifies the number of the column containing the output value that we want to look up, and [range_lookup] is a true or false value – where TRUE denotes an approximate match and FALSE is an EXACT match.

2. Importance of Headers

Here, we will see the importance of declaring the source dataset with column headers for the DGET and VLOOKUP functions.

2.1. DGET Function

We will try to look up the NTRP Rating value for the player Beverly Morgan among other players’ names in the dataset using the DGET function.

• Apply the following formula in cell D4.

Here, B6:F88 is the dataset starting from the column header name, D3 is the cell reference for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting the correct NTRP Rating of 3.5 for the player Beverly Morgan.

Instead of using the former formula if we use the following formula,

where we have changed the dataset reference to B7:F88 by ignoring the column header names.
In return, we are getting the #VALUE! error for ignoring the column headers’ names in the dataset argument position.

2.2. VLOOKUP Function

You can try to look up the NTRP Rating value for the player Beverly Morgan among other players’ names in the dataset using the VLOOKUP function.

• Apply the following formula in cell D4.

Here, C4 is the lookup value, C6:F88 is the table_array starting from the column header name, 4 is the column index number, and FALSE is for an exact match.
As a result, we are getting the correct NTRP Rating of 3.5 for the player Beverly Morgan.

If we change the reference of the table_array argument,

Here, we have changed the table_array argument to C7:F88 excluding the column headers’ names and as a result, we are getting the same result as the previous one.

3. Flexibility of Using Column Number or Header in DGET vs VLOOKUP Functions in Excel

Here, we will show the differences between the DGET and VLOOKUP functions by using the column number or the header name of the column from which we want to extract our look-up value.

3.1. DGET Function

We will try to look up the NTRP Rating value for the player Isabella Johnson among other players’ names in the dataset using the DGET function.

• Apply the following formula in cell D4.

Here, B6:F88 is the dataset starting from the column header name, D3 is the cell reference for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting the correct NTRP Rating of 3 for the player Isabella Johnson by using the Field name as the name of the column.

Instead of using the former formula if we use the following formula,

where we have changed the Field argument to 5 and we have got a similar correct result.

3.2. VLOOKUP Function

You can try to look up the NTRP Rating value for the player Isabella Johnson among other players’ names in the dataset using the VLOOKUP function.

• Apply the following formula in cell D4.

Here, C4 is the lookup value, C6:F88 is the table_array starting from the column header name, 4 is the column index number, and FALSE is for an exact match.
As a result, we are getting the correct NTRP Rating of 3 for the player Isabella Johnson.

If we change the reference of the column index argument,

Here, we have changed the column index argument to D3 to use the column name instead of the column number in the dataset.
As a result, we are getting the #REF! error.

4. Column Direction for Look Up Values in DGET vs VLOOKUP Functions in Excel

Generally, the VLOOKUP function looks up the value in the right direction but you can use the DGET function to look up the value in both directions.

4.1. DGET Function

Using the DGET function we will try to look up the NTRP Rating by moving to the right direction and for gaining the Player ID we will move to the left direction. Here, in the dataset, the column containing the value for which we looking at other data has not to be in the first position.

• Apply the following formula in cell D4.

Here, B6:F88 is the dataset starting from the column header name, D3 is the cell reference for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting the correct NTRP Rating of 4 for the player Joe Patterson by going from the column Player Name to the right to the column NTRP Rating.

• Use the following formula in cell E4.

Here, B6:F88 is the dataset starting from the column header name, D3 is the cell reference for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting the correct Player ID of #1007 for the player Joe Patterson by going from the column Player Name to the left to the column Player ID.

4.2. VLOOKUP Function

Using the VLOOKUP function we will try to look up the NTRP Rating by moving in the right direction and for gaining the Player ID we will move to the left direction. Here, in the dataset, the column containing the value for which we looking at other data has to be in the first position.

• Apply the following formula in cell D4.

Here, C4 is the lookup value, C6:F88 is the table_array starting from the column header name, 4 is the column index number, and FALSE is for an exact match.
As a result, we are getting the correct NTRP Rating of 4 for the player Joe Patterson by going from the column Player Name to the right to the column NTRP Rating.

• Enter the following formula in cell E4.

Here, C4 is the lookup value, B6:F88 is the table_array starting from the column header name, 1 is the column index number, and FALSE is for an exact match.
As a result, we are getting the #N/A error for going from the column Player Name to the right to the column Player ID.

5. Importance of Unique Values

We will try to show the importance of unique values and the effect of duplicate values in a range for both the DGET and VLOOKUP functions.

5.1. DGET Function

We will try to look up the NTRP Rating value for the player Isabella Johnson among other players’ names in the dataset using the DGET function. Here, we have this name twice in our main dataset.

• Apply the following formula in cell D4.

Here, B6:F88 is the dataset starting from the column header name, 5 is the column number for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting the #NUM error because of the duplicate names in the dataset.

5.2. VLOOKUP Function

You can try to look up the NTRP Rating value for the player Isabella Johnson among other players’ names in the dataset using the VLOOKUP function.

• Apply the following formula in cell D4.

Here, C4 is the lookup value, C6:F88 is the table_array starting from the column header name, 4 is the column index number, and FALSE is for an exact match.
As a result, we are getting the first NTRP Rating of 3 for the player Isabella Johnson.

6. Getting Multiple Outputs in DGET vs VLOOKUP Functions in Excel

Using the DGET function you cannot get multiple outputs at a time, but you can do it by using the VLOOKUP function.

6.1. DGET Function

We will try to look up the Gender and Age of the player Eddie Fischer among other players’ names in the dataset using the DGET function. As we can not do it at a time, we will do it twice.

• Apply the following formula in cell D4.

Here, B6:F88 is the dataset starting from the column header name, D3 is the reference of the column name for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting Male as Gender for the player Eddie Fischer.

• To extract the age enter the following formula in cell E4.

Here, B6:F88 is the dataset, E3 is the reference of the column name for the Field name, and C3:C4 is the range of Criteria with the header name for which we are looking the value.
As a result, we are getting 24 as the Age for the player Eddie Fischer.

6.2. VLOOKUP Function

We will try to look up the Gender and Age of the player Eddie Fischer among other players’ names in the dataset using the VLOOKUP function at a time.

• Apply the following formula in cell D4.

Here, C4 is the lookup value, C6:F88 is the table_array starting from the column header name, 4 is the column index number, and FALSE is for an exact match.
As a result, we are getting Male as the Gender and 24 as the Age for the player Eddie Fischer.

Note: For using any other versions except Microsoft Excel 365 you have to press CTRL+SHIFT+ENTER instead of pressing ENTER.

7. Looking up Multiple Criteria in DGET vs VLOOKUP Functions in Excel

Here, we will try to extract a value after checking multiple criteria by using these two functions.

7.1. DGET Function

Here, we will check Player ID #1007 and Player Name, Joe Patterson, while using the DGET function to get the NTRP Rating. Moreover, by using these two criteria you can avoid the error of duplicate values.

• Apply the following formula in cell E4.

Here, B6:F88 is the dataset starting from the column header name, E3 is the reference of the column name for the Field name, and C3:D4 is the range of Criteria with the header name for which we are looking for the value.
As a result, we are getting the NTRP Rating of 4 for the player Joe Patterson with ID #1007.

7.2. VLOOKUP Function

Here, we will check Player ID #1007 and Player Name, Joe Patterson, while using the VLOOKUP function to get the NTRP Rating. Moreover, by using these two criteria you can avoid the error of duplicate values.

• Apply the following formula in cell E4.

Here, C4:D4 is the lookup value, B6:F88 is the table_array starting from the column header name, 5 is the column index number, and FALSE is for an exact match.
As a result, we are getting the NTRP Rating of 4 for the player Joe Patterson with ID #1007 but besides this, the #N/A error is also shown.

Note: For using any other versions except Microsoft Excel 365 you have to press CTRL+SHIFT+ENTER instead of pressing ENTER.

What Are the Advantages and Disadvantages of the DGET Function?

Advantages

• Doesn’t depend on the direction or position of columns.
• It can handle multiple criteria and larger data sets with ease.

Disadvantages

• For any formula that you have to pull down throughout the cells, this function is not good for this case.
• It automatically works on an approximate match.

What Are the Advantages and Disadvantages of the VLOOKUP Function?

Advantages

• You can work with an exact match or an approximate match as per your requirement.
• This formula can be copied down easily.
• It is easy to learn, additionally, many Excel users are familiar with the function.

Disadvantages

• It depends on the direction or position of the columns.
• The VLOOKUP function can be prone to errors in larger spreadsheets.

Download Practice Workbook

Conclusion

In this article, we tried to show the differences between the DGET vs VLOOKUP functions in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

<< Go Back to Lookup | Formula List | Learn Excel