• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel DGET Function

You are here: Home / Excel Formulas / Excel DGET Function
DGET Function
October 9, 2019 by Philip Treacy

Typically if you need to lookup a value from a table you'd employ VLOOKUP or INDEX/MATCH.

However if your data looks like this

Source Data

and you are looking up by Employee name then you'll run into some issues because there are two employees with the name John.

Both the VLOOKUP and INDEX/MATCH approach will have problems with this because they need to lookup unique values.

You can use VLOOKUP or INDEX/MATCH to lookup multiple values across multiple columns but this requires writing a complicated (legacy) array formula using CTRL+SHIFT+ENTER

So, what to do? If you have the new FILTER function you could use that.

FILTER allows you to return all records that match your criteria, so in this case you could return the data related to both John's. But if you are just interested in the John with the highest Rating, FILTER will also allow you to specify additional criteria to give you the data you are after.

The key here is that FILTER lets you look for more than one thing in your source data (for example Employee="John" and Target=12) and return the data in that record.

Most people don't yet have FILTER though so this is where DGET comes in.

DGET allows you to look up a value in a table (or a range/list of records) based on multiple criteria, just like FILTER. However DGET only returns a single value, not an entire row of data.

Excel Dynamic Arrays

FILTER is one of the new Excel Dynamic Arrays family of functions.

Dynamic Arrays are currently only available in Office 365 to a portion of Office Insiders.

Excel 2019 will not have the Dynamic Array functions.

DGET Syntax

The syntax to use DGET is

DGET Syntax

where the arguments are

  • database - a table/range including headers.
  • field - field/header name or numeric column index.
  • criteria - criteria range including headers.

DGET Examples

As shown previously I have a table of data like this, I've named the table Employees. Note that your data does not have to be in a table for DGET to work.

Source Data

We have two employees named John. As stated earlier, neither VLOOKUP nor INDEX/MATCH can be relied upon to return the correct result if we want to look up something related to one of the John's.

Putting Stuff Under Tables

If you put data or formulae under a table and rows are added to the table, your data/formulae will get shifted down. This may or may not break something on you so be aware.

If I try to use DGET to lookup the Rating for John, it returns a #NUM! error because it doesn't know which John I want.

DGET Example

DGET NUM Error

So I also specify Target is 12 and I get the answer 52% for the Rating.

DGET Using Two Criteria

DGET Using Two Criteria Result

Notice that when specifying the criteria you must include the header/name of the column as well as the data, this is why the 3rd argument is the range A9:B10.

The 2nd argument for DGET is the data/field you want the function to return. In the example above I reference cell D9 which contains the word Rating, so I want the Rating returned.

I could replace the word Rating with the number 4 to indicate I want the data in the 4th column of the table returned. Of course you could just type in 4 to the DGET formula rather than reference cell D9.

Criteria

Expressions can be used in criteria such as

 Jo* Matches a string starting with "jo" (case insensitive)
12 Equal to 12
<>12 Not equal to 12
<12 Numbers less than 12
<> Not blank
< 28/10/2008 Dates before October 28 2008

String matching is case insensitive so "John" will match "John" and "JOHN".

Multi-Row Criteria

DGET Multi Row Criteria

The criteria for DGET can include more than one row below the headers.

In such a scenario, the conditions in a row are joined using AND, and each row is joined using OR.

Taking the example image above, I am looking for the employee (either Alison, Bill or Lisa) whose Target is greater than 20 and whose Rating is greater than 100%.

So the criteria we have are

DGET Multi Row Criteria example

If we check these against the source data table, we find that there is an Employee named Alison (TRUE), her Target is not greater than 20 (FALSE) and her Rating is greater than 100% (TRUE).

Doing this for all 3 rows we can simplify to

DGET Multi Row Criteria Boolean Values by Row

Combining the conditions within each row to give a True/False we get

DGET Multi Row Criteria Boolean Values

The only criteria that gives a True result is the 2nd one so the value returned by DGET is the Employee that matches that, Bill.

Download Sample Workbook

Enter your email address below to download the workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Examples of using DGET from this post are in the sample workbook.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

DGET Function

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:Excel Goal SeekExcel Goal Seek
Next Post:Reducing Data Validation Listreducing data validation list

Reader Interactions

Comments

  1. Ravindra singh

    April 26, 2020 at 9:04 pm

    How to drag dget function in row such as vlookup?

    I

    Reply
    • Philip Treacy

      April 27, 2020 at 9:57 am

      Not sure what you mean?

      Reply
  2. Annie Anderson

    November 8, 2019 at 10:32 am

    Thanks for explaining this function! I’m currently using a VLOOKUP that uses “Employee Name” to return their “Position Number”. My challenge is that sometimes my source document name is different than my position number workbook. For example, my source document lists the name as Brown, Mary and my position number spreadsheet has the name as Brown, Mary J, which returns a “N/A error because of the middle initial. Can you suggest the best VLOOKUP-type formula that will work in this scenario without using Text to Columns and the CONCATENATE formula? Thanks for your help.

    Reply
    • Philip Treacy

      November 8, 2019 at 10:49 am

      Hi Annie,

      The best solution is to get your source data to match your other workbook, rather than modifying formulae to accommodate multiple variations of a name. Can’t the source be restricted to ensure data is consistent? Really that is the best approach.

      Regards

      Phil

      Reply
  3. Rob Rascon

    October 17, 2019 at 7:45 am

    Thank you, Phil, this is very helpful and I’m anxious to test this a bit more. We have Office 365, but I’m not sure why the FILTER function is not available. Anyway, for DGET, my first question is does the criteria have to be in a table/range or can it be manually entered? And if so, do you have some information on the syntax for entering the criteria directly into the formula?

    Reply
    • Philip Treacy

      October 17, 2019 at 10:25 am

      Hi Rob,

      You have to supply a range as the criteria, you can’t manually type it into the formula.

      FILTER is currently only available to some Office 365 subscribers. See the blue info box ‘Excel Dynamic Arrays’ at the top of the post for more information.

      Regards

      Phil

      Reply
      • Rob Rascon

        October 18, 2019 at 3:37 am

        ok great, thanks!

        Reply
  4. Patrick Schommer

    October 10, 2019 at 5:09 am

    I tried duplicating the DGET example from your sheet and it didn’t work (#VALUE!).
    I was wondering why you put the “[#All]” after the table name, so I ignored it and just used the name of the table by itself. It seems that the [#All] section part of the table identifier is absolutely necessary!

    Adding it with Intellisense is easy and it also exposed me to the other values (#All, #Data, #Headers, #Totals), so thanks for that tidbit.

    The mulit-row criteria example is very similar (identical?) to the Advanced Filter capability with AND and OR functionality.

    Great tip!

    Reply
    • Philip Treacy

      October 10, 2019 at 12:11 pm

      Hi Patrick,

      [#All] is required because DGET needs the entire table, including headers, not just the data. Using just Employees would be referencing just the data in the table.

      See Table Structured References for more info.

      Regards

      Phil

      Reply
  5. Choua

    October 9, 2019 at 11:46 pm

    Thanks so much for sharing, Philip! Before reading this article, I was using SUMPRODUCT in a similar fashion to find what I was looking for when I had multiple criteria. However, it didn’t work if there was a single non-numeric value in the column that I was trying to pull results from (so in your example, if there was a NULL or #DIV/0 error in column D).

    I really appreciate that this will work for non-numeric values as well. My question is this, if I want to find multiple results (for example, for both John and Lisa each), I would have listed them in separate rows and then used the SUMPRODUCT formula with appropriate references to grab their respective results; what would be the best way to write the formula for the second person since I need to include the header?

    (So pretend with me that we are using the first example. I would keep row 10 to find one of the John’s information. Then, I would have Lisa’s name in A11, with 23 in B11; what would be the formula in D11?)

    Thanks in advance!

    Reply
    • Philip Treacy

      October 10, 2019 at 12:34 pm

      No worries Choua, glad you find this useful.

      Your question hits upon one of the limitations of DGET, and why FILTER is better in such a scenario. Because you have to provide the Header/column name in the criteria, doing this over multiple lines is difficult.

      Before I answer your qs, I should say it’s best not to put anything below the last row of a table. In fact it’s best not to put anything below a table in case more rows are added, I’ve only done this in these examples to make demonstration easier.

      But, with your example, let’s say you have your criteria like this starting in Row 12

        A B C D
      12 Employee Target Actual Rating
      13 John 12 =DGET(Employees[#All],D12,A12:B13)

       

      You can’t add Lisa to Row 14 because the Header row is row 12. You could either use a data validation list in A13, B13 etc showing the data from the Employees table. Changing these will change the result you get in D13.

      Or you will have to add another row of Headers like so

        A B C D
      12 Employee Target Actual Rating
      13 John 12 =DGET(Employees[#All],D12,A12:B13)
      14
      15 Employee Target Actual Rating
      16 Lisa 23 =DGET(Employees[#All],D15,A15:B16)

       

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.