Considering myself a pretty advanced excel user who can write vlookups and index-match statements in my sleep and still have never heard of this. This is amazing, no more helping columns! Love it!
Great info! Spent years grinding Index/Match lookups and never ever heard of DGET. Now I have to watch all of your other vids to see what else I've been missing.
Welcome aboard, John 👊 INDEX-MATCH is a better formula and far more flexible than DGET. But DGET is simpler for this specific instance on creating summaries for Dashboards and Reports.
Mate, I wish I'd seen this video a few days ago. I ended up using CHOOSE inside a VLOOKUP inside an IF statement (!!) This is brilliantly straightforward. Thank you so much!
I 2nd Dougdevine27's comment. I am a heavy and fairly advanced user and done things with excel where people said things like, "i didn't know you could do that in excel" but I had never heard of this function. Great job!!! 👍
I was aware dget can replace vlookup and other functions. However, its ability to handle and replace array formulas is certainly something we should appreciate..!!! Thanks for sharing.
Thank for deeply explaining with good example. If possible, please add more relevant group of D* such as Dget, Dsum, Dmin, and Dmax. Thank again for kind sharing knowledge.
Great. Thanks for sharing. Whilst I can see the many advantages of DGET, the biggest disadvantage as far as I can see (unless I am missing something) is that you can't copy the formula down
@@loriperezlp yes, I am aware of that Lori... when it eventually becomes widely available. I have MS Office 365 ProPlus, but it's still not available to me!
Watching this video has really been an eye opener. I never hears about the DGET function and its wonderful features till now. However, the search is only on one row. What if I have data to search for on the the second or third rows, if I pull the formula down to the next row, would the formula still work?
Since you cannot drag down the formula (e.g. if you had Produce Canada in row 5), what’s the solution to that? I also don’t want to use a helper column. Thanks
Thanks for the great video! In your examples you have a single line of conditions for DGET (Produce in Denmark), but what if you were making a list of things, similar to a pivot table? Could you Put Produce in Canada underneath and still construct a DGET that will skip over the first row of measures and only look up the second row?
Great video! Just one query - In the example 9mins in could you put other variables from row 4 onwards (under Product and Country) and would the DGET formula return the different results for sales in cells H4, H5, etc??? Many thanks for your help in advance and hope not too annoying!
THANKS, ITS REALLY GOOD INSTEAD OF VLOOKUP WE CAN USE THIS, BUT HOW ABOUT IF I WANT TO DRAG THE SAME FORMULA BELOW THE SAME LINE WILL BE TAKE OTHER RESULT TOO FOR "PRODUCE CANADA" DATA
Hi, just to check. under this Dget function can it work in a diff workbook or worksheet? exp: I want to use the main worksheet get the info from others workbook. This Dget can it work in this way?
Very useful! Re example 2...Sumproduct CAN BE USED with text and arguably can handle much more complex datasets with multiple matching values and also for creating summary tables. I point it out bc I use it daily with large DBs and it's a life saver. An example formula of using Sumproduct for the 2nd example (also will handle multiple matching values) is: =SUMPRODUCT(--($F4=$A:$A),--($G4=$B:$B),$C:$C)
Thank You kindly for this advice. This might be a solution to a problem I'm tryng to solve myself right now. Do You think on such formula could work across 3 different sheets? Or should I built 3 separate formulas for each? I have to handle the same data in 3 different sheets but additional problem is that even if a column refers to the same thing and includes the sae value the column naming system isn't coherent. What's more I have to deal with lack of possibility to use Vlookup option.
@@femmeNikita27 I don't think you could use sumproduct with multiple sheets in the same function but could have multiple sumproduct functions in the same cell if that makes sense?
@@JBalshaw22 Yes, makes sense. Thank You for prompt reply. I'm struggling with what I have in lack of more advanced options I know, so every suggestion which might be useful is priceless to me. Once more, highly appreciated help. I will put it to the test asap.
Hi Alan.. thanks for the clear and concise intro to DGET(). It is a new one for me and after watching your video, I can already think of ways to use it in my work.. excellent. Thumbs up!
Hello, I'd like to ask, what function would you use if you wanted to do the second example but you had multiple sales in the same place for the same product, without grouping them in another table first? Is it possible?
Just to confirm would it return multiple rerecords as well? Let's say I select a company name from a drop down list or Combo Box then it returns the list of all it's employees who work there with their first name, last name, title, phone, email address,.........from another worksheet? Is there an easier way to achieve this goal?
Thanks Jon. Yes this function works in 2010. Both DGET and INDEX/MATCH. They are not exactly the same. INDEX/MATCH is more versatile an dpopular as it can be used with list - which Excel users normally are. DGET is a summary function so great for dashboards as easier than INDEX/MATCH especially for multi-conditions. But cannot be copied down columns.
You're welcome. It depends on the situation. This is a database function so great for summarising data, INDEX & MATCH more for retrieving data for lists.
Can’t believe how I didn’t know about this! One question though, if I am pulling back multiple results in a table how do I make my criteria change on each row? As from the second row onwards the field name and criteria will not be adjacent to each other?
Absolutely. This function does not return multiple results in rows in the way that VLOOKUP or INDEX can. Its strength lie in reporting scenarios and handling multiple criteria.
SUMPRODUCT can be made to work with any value. Simply convert the Boolean to binary using a double negative. E.g. --($A$2:$A$50="Apples"). You can return the row of the match by adding a ROW($A$2:$A$50) to your arguments list which makes the SUMPRODUCT now work with an INDEX.
Is there another way of inputting the criteria apart from as a range? It would be ideal if you didn't have to use any cells to call the function, which is why I don't really use the advanced filter functionality either
Question: At 7:74 you say, "let me fix that" and instantly the $ sign appears in the formula. How? Did you pause the video and type them in or is there a short-cut key that does it?
They can do. PivotTables summarise values by categories, and you can provide multiple categories as you say. A table of results. SUMPRODUCT can produce a result to a single cells and the criteria can get more complex however.
Hi, what if there are multiple values to the criterias? Is there a way to extract out all the values with the same criteria without using a pivot table?
I often compare the database functions with SUMPRODUCT and SUMIFS and so on to show that as we add more criteria, the database functions never balloon in size, always database,field,criteria whereas the others get longer and longer and more complex. Database formulas win!
Thank you. It won't do the Sum although no reason why you couldn't have a formula pointing a the return range. Alternatively as you say a SUMIFS or SUMPRODUCT.
@@Computergaga Very true. Btw, since only recently I have grasped the power and utility of Excel, I have been helping my department to automate all the reports. Any source you would recommend from where I can learn Macro? Thank you!
Very good question. I would add to it : can it do so for multiple criteria in rows across multiple sheets? since this is kind of vlookup alternative I'm looking for now.
Primarily yes. Useful for dashboard scenarios and input cells in models. Not ideal for lists where you want to copy the formula down multiple rows in a column. Leave that for your XLOOKUP, INDEX-MATCH and VLOOKUP.
very very intersting. been using excel a LOT for data analytic and never used DGET. would you know if the "memory use" of DGET is better that Vlookup ?
It does use a little more memory than vlookup -mostly because of the size of the domain (range) it can accommodate. All domain functions (Dget, vlookup, hlookup, maxifs, dlookup, sumproduct) use up a fair amount, but if used wisely they are great tools in the kit.
Considering myself a pretty advanced excel user who can write vlookups and index-match statements in my sleep and still have never heard of this. This is amazing, no more helping columns! Love it!
Happy to help 👍
I can make Excel sing with the best of them and I have never heard of DGET. Well done, mate!
Thanks Doug. Excel has a beautiful voice.
Probably because new versions of Excel try so hard to hide the help file from the user.
Great info! Spent years grinding Index/Match lookups and never ever heard of DGET. Now I have to watch all of your other vids to see what else I've been missing.
Welcome aboard, John 👊 INDEX-MATCH is a better formula and far more flexible than DGET. But DGET is simpler for this specific instance on creating summaries for Dashboards and Reports.
Mate, I wish I'd seen this video a few days ago. I ended up using CHOOSE inside a VLOOKUP inside an IF statement (!!) This is brilliantly straightforward. Thank you so much!
seriously, that was so simple
I've been working with Excel for almost 8 years now and never heard of DGET() before, this will definitely make life easier... Thank you so much!
My pleasure Luis.
Bravo. Thanks for showing me something new! I've been using Excel for years and never heard of DGET.
You're welcome George. Thank you.
I 2nd Dougdevine27's comment. I am a heavy and fairly advanced user and done things with excel where people said things like, "i didn't know you could do that in excel" but I had never heard of this function. Great job!!! 👍
Thank you.
I was aware dget can replace vlookup and other functions. However, its ability to handle and replace array formulas is certainly something we should appreciate..!!!
Thanks for sharing.
My pleasure Ahmed.
Never heard about such a super formula, thanks for sharing
My pleasure Siddharth.
Thank for deeply explaining with good example. If possible, please add more relevant group of D* such as Dget, Dsum, Dmin, and Dmax. Thank again for kind sharing knowledge.
Great. Thanks for sharing. Whilst I can see the many advantages of DGET, the biggest disadvantage as far as I can see (unless I am missing something) is that you can't copy the formula down
This is true. Was designed as a summary function rather than for lists.
Hi Steve, You can use "Xlookup" function for lists. This replaced "Vlookup" for me.
@@loriperezlp yes, I am aware of that Lori... when it eventually becomes widely available. I have MS Office 365 ProPlus, but it's still not available to me!
@@loriperezlp oh can we? *as we all sit here waiting for xlookup* :)
It's good to know but not a replacement for vlookup. what to do when you have to apply the same in multiple rows? still worth sharing. keep it up.
The major issue is with Excel defines the "Criteria" for DGET. It should have been a simple condition.
You do realise I've got to rewrite all my spreadsheets now! Really useful stuff!
Sorry buddy 😀
Sounds fun! 😁
🎉 Very good! I was vaguely aware of this formula but haven't used it. Until now!
Thank you 😊
Watching this video has really been an eye opener. I never hears about the DGET function and its wonderful features till now.
However, the search is only on one row. What if I have data to search for on the the second or third rows, if I pull the formula down to the next row, would the formula still work?
I'm afraid not Tochukwu. This lookup is more for your summary uses.
Love Excel. Thanks for this. I didn't know about DGET
Thank you Karen.
It's fantastic. This function is a combination of some features of vlookup and advanced filter. thanks for sharing
Thank you. You're welcome Sachin.
@@Computergaga .. hi it's working only for 1st row not for next
Crystal Clear Sir. Thank You
You're welcome Panagiotis.
Like your style man, very cool and to the point, no faffin, 🙏🏽thanks
I appreciate that. Thank you.
Since you cannot drag down the formula (e.g. if you had Produce Canada in row 5), what’s the solution to that? I also don’t want to use a helper column. Thanks
Thanks for the great video! In your examples you have a single line of conditions for DGET (Produce in Denmark), but what if you were making a list of things, similar to a pivot table? Could you Put Produce in Canada underneath and still construct a DGET that will skip over the first row of measures and only look up the second row?
I wanted to do this, too :(
Great video! Just one query - In the example 9mins in could you put other variables from row 4 onwards (under Product and Country) and would the DGET formula return the different results for sales in cells H4, H5, etc??? Many thanks for your help in advance and hope not too annoying!
THANKS, ITS REALLY GOOD INSTEAD OF VLOOKUP WE CAN USE THIS, BUT HOW ABOUT IF I WANT TO DRAG THE SAME FORMULA BELOW THE SAME LINE WILL BE TAKE OTHER RESULT TOO FOR "PRODUCE CANADA" DATA
Cool trick 👍 but just a question, is it possible to drag down to search for multiple items?
Yes, jus lock the reference heading cell column and keep the rows unlocked using f4
Hi, just to check. under this Dget function can it work in a diff workbook or worksheet? exp: I want to use the main worksheet get the info from others workbook. This Dget can it work in this way?
Very cool. You taught this old dog a new trick.
Thank you.
Nice. Much easier alternative to array formulas.
Yes, definitely
Thanks for posting. So we have to have a second table to indicate the criteria? cannot we just write"product", "name" within the criteria?
Yes, this function operates from a criteria range.
nice function gonna use it monday was doing it with a long index and match formula now thanks for the tip
Happy to help Martijn.
Very useful! Re example 2...Sumproduct CAN BE USED with text and arguably can handle much more complex datasets with multiple matching values and also for creating summary tables. I point it out bc I use it daily with large DBs and it's a life saver.
An example formula of using Sumproduct for the 2nd example (also will handle multiple matching values) is:
=SUMPRODUCT(--($F4=$A:$A),--($G4=$B:$B),$C:$C)
SUMPRODUCT with text criteria is probably one of the most common functions I use in Excel. Very helpful!
Thank You kindly for this advice. This might be a solution to a problem I'm tryng to solve myself right now. Do You think on such formula could work across 3 different sheets? Or should I built 3 separate formulas for each? I have to handle the same data in 3 different sheets but additional problem is that even if a column refers to the same thing and includes the sae value the column naming system isn't coherent. What's more I have to deal with lack of possibility to use Vlookup option.
@@femmeNikita27 I don't think you could use sumproduct with multiple sheets in the same function but could have multiple sumproduct functions in the same cell if that makes sense?
@@JBalshaw22 Yes, makes sense. Thank You for prompt reply. I'm struggling with what I have in lack of more advanced options I know, so every suggestion which might be useful is priceless to me. Once more, highly appreciated help. I will put it to the test asap.
@@femmeNikita27 You're very welcome, let me know how you get on.
Never heard of this function. It's amazing;)
👍
Cool, never heard of DGET. Excels library is vast. Thx for sharing the knowledge.
My pleasure Tahj.
Hi Alan.. thanks for the clear and concise intro to DGET(). It is a new one for me and after watching your video, I can already think of ways to use it in my work.. excellent. Thumbs up!
Thanks Wayne.
Hello, I'd like to ask, what function would you use if you wanted to do the second example but you had multiple sales in the same place for the same product, without grouping them in another table first? Is it possible?
skypse LaKlikariaLoca: pivot tables are very useful for those scenarios, and like everything in excel, easy to use with a little practice.
Absolutely brilliant
Thank you 😊
Solved a problem I was having with Vlookup! thanks!
Excellent
Awesome.. Learned something new and valuable today.. Never heard and didn't knew it was so easy.. Thanks..
Very useful. Just wanted to know why can't we use data validation drop down lists in this second one.??
I tried but didn't get the answer....
You're right. Strange. Not sure why it doesn't work.
Great. Thank you and I cant wait to start using this formula.
Happy to help Ardi.
holy cow, this is what I have been looking in excel, all this time!! thank you!
You're welcome Eriol.
Really thank you s much sir.... Very usefull formula
You're very welcome. Thank you.
Just to confirm would it return multiple rerecords as well? Let's say I select a company name from a drop down list or Combo Box then it returns the list of all it's employees who work there with their first name, last name, title, phone, email address,.........from another worksheet? Is there an easier way to achieve this goal?
Excellent video. Thank for sharing. Never heard of DGET.
Thank you David.
Very good ... Thank you
Thank you Joeey.
Cool mate, is this any benefit to INDEX MATCH or the same? and does this work on 2010 as my company is still on that believe it or not!!
Thanks Jon. Yes this function works in 2010. Both DGET and INDEX/MATCH. They are not exactly the same. INDEX/MATCH is more versatile an dpopular as it can be used with list - which Excel users normally are. DGET is a summary function so great for dashboards as easier than INDEX/MATCH especially for multi-conditions. But cannot be copied down columns.
Computergaga thanks mate I will have a play with it
Thanks for clear explaination. It's really helpful. Instead of index and match, can i use dget function.
You're welcome. It depends on the situation. This is a database function so great for summarising data, INDEX & MATCH more for retrieving data for lists.
Can’t believe how I didn’t know about this! One question though, if I am pulling back multiple results in a table how do I make my criteria change on each row? As from the second row onwards the field name and criteria will not be adjacent to each other?
Absolutely. This function does not return multiple results in rows in the way that VLOOKUP or INDEX can. Its strength lie in reporting scenarios and handling multiple criteria.
many thanks Alan, I did not know dget existed, a great tool and a great tutorial as always. Cheers Mohideen
Thanks Mohideen.
very useful video. great 👌👍
Thank you 😊
SUMPRODUCT can be made to work with any value. Simply convert the Boolean to binary using a double negative. E.g. --($A$2:$A$50="Apples"). You can return the row of the match by adding a ROW($A$2:$A$50) to your arguments list which makes the SUMPRODUCT now work with an INDEX.
Wonderful tutorial
Thank you Sam.
SUPERB!!! NEW ADDITIONAL KNOWLDGE this is really a Secret VLOOKUP alternative
Thank you Jaipal.
Only works if you are only looking for one id. If you have multiple ids to lookup, you cannot drag the formula down.
Wow, amazing, I never used this before but i will definitely try this. Thumbs up
Excellent! Thank you Khalid.
Thanks for your video very nice. Sir How can we add Kg, Pcs, Cotton etc with Numbers in Single Cell Please Tell me.
You can use Custom Formatting such as in this video - ruclips.net/video/EG_zpxfxR4k/видео.html
Thanks for sharing this valuable information
You're very welcome.
can you give example with next button change data from table to cell every click next button?
Very good, I can see applications for this
Thanks Clive.
Hi Can you help me getting my data sorted in horizontal Criteria & Vertical criteria using Dget function ?
I'm not sure what you are asking Mo. Sounds like you may want to transpose your data.
Fantastic job sir! Thanks. 👍
You're welcome Ashish. Thank you.
Would the database part become dynamic if it were made into a table that can be updated?
Can we use dget if the table is in a different workbook
Is there another way of inputting the criteria apart from as a range? It would be ideal if you didn't have to use any cells to call the function, which is why I don't really use the advanced filter functionality either
All I have to say is WOW!! Thank you.
You are more than welcome Georgia.
Question: At 7:74 you say, "let me fix that" and instantly the $ sign appears in the formula. How? Did you pause the video and type them in or is there a short-cut key that does it?
Shortcut key is F4 👍
you deserved to be subscribed
Thank you Yosef.
@@Computergaga because you helped people. thank you too 👌(from the Philippines)
Nicely done. Simple question, can you use data validation lists for the multiple criteria fields? I'd assume so.
Yes absolutely Chris. You assume correctly.
Thank you. Have a good one.
I had been using sumproduct to extract information based on multiple criteria. However, aren't pivots serving the same purpose?
They can do. PivotTables summarise values by categories, and you can provide multiple categories as you say. A table of results. SUMPRODUCT can produce a result to a single cells and the criteria can get more complex however.
Amazing !! Never heard of this function before.
Thank you.
Hi, what if there are multiple values to the criterias? Is there a way to extract out all the values with the same criteria without using a pivot table?
I'm afraid not with this function Devian.
Nice sharing...… I did not knew about this secret function of excel
Excellent! You're welcome.
I often compare the database functions with SUMPRODUCT and SUMIFS and so on to show that as we add more criteria, the database functions never balloon in size, always database,field,criteria whereas the others get longer and longer and more complex. Database formulas win!
Yes, they sure are useful for this Duncan.
Definetely thumb up for this video sir!
Thank you Miroslav.
How would you apply dget, if there are multiple rows, and criteria changes for each row.
You cant. This is not a complete alternative for vlookup.
I suppose separate DGET formula for each case. Unless we do more digging in the issue and figure out something else.
Love this formula, great video ✔
Thank you.
Awesome thanks for.nice presentation.
You're welcome. Thank you Gurrappa.
Insane! Thanks a ton! Can DGET do sum like SumIFs when it finds multiple rows with same criteria?
Thank you. It won't do the Sum although no reason why you couldn't have a formula pointing a the return range. Alternatively as you say a SUMIFS or SUMPRODUCT.
@@Computergaga Very true. Btw, since only recently I have grasped the power and utility of Excel, I have been helping my department to automate all the reports. Any source you would recommend from where I can learn Macro? Thank you!
Great to hear. Sure, I have an Excel VBA course - bit.ly/2JvnnRv
Wow!!!!!!!!! I never knew this. Fab work sir. Thanks
Thank you Mike.
Been using vlookup for a long time, always annoyed by the limitation. Thanks for the video, learned a better replacement for vlookup!
dear its not like replacement. vlookup is far better then this one.
Very useful!!!
Thank you.
Very helpful. thanks.
No problem Maria.
Can DGET have multiple criterior in rows as opposed to columns?
Very good question. I would add to it : can it do so for multiple criteria in rows across multiple sheets? since this is kind of vlookup alternative I'm looking for now.
Excellent.
Thank you very much Khalid.
Superb! Thanks.
You're welcome Chris.
Well sir I have question is it for single cell purposes
Primarily yes. Useful for dashboard scenarios and input cells in models. Not ideal for lists where you want to copy the formula down multiple rows in a column. Leave that for your XLOOKUP, INDEX-MATCH and VLOOKUP.
Thanks for sharing .
You're welcome Hemanth.
Thanku today I have learnt new function, dget.
Awesome. Great to hear Datta.
Can this formula be used to return values for multiple cells?
Not really. More of a summary function than lists like VLOOKUP.
Hi brother,
I need help with a case. Please let me know if you could help me with it.
Thanks,
Saqib
very very intersting. been using excel a LOT for data analytic and never used DGET.
would you know if the "memory use" of DGET is better that Vlookup ?
Thank you. I am not sure on the memory use.
Computergaga i will test :)
It does use a little more memory than vlookup -mostly because of the size of the domain (range) it can accommodate. All domain functions (Dget, vlookup, hlookup, maxifs, dlookup, sumproduct) use up a fair amount, but if used wisely they are great tools in the kit.
Sir,
How we can get data from daybook, to create party wise ledger, with sales details and receipt details. Please help me
is it usable if the data is not in the same sheet?
Wow this is fantastic!
Thank you Roger.
I use index-match all the time to match by row and/or column. This DGET function looks to be useful, thanks.
You're welcome Stan.
Cool man, another way of extracting data on multiple criteria
Thanks Jon.
If possible, I want to click "like" for 100 times!👍👍👍+++
Thank you Stephanie.
Thanks Alan may include this in my intermediate course 😉
Thank you.
Please put all excel practiced files in the description.
Thanks
You're welcome Dumpala.
You’re amazing. Plus 1 subscriber 💪💪💪
Thank you Pap.
Hi how to deal when the lookup value is not in a column. Rather spread across a table