DGET Function - The Secret VLOOKUP Alternative for Multiple Conditions
Вставка
- Опубліковано 24 сер 2024
- In this video, we look at the DGET function - the secret alternative to VLOOKUP for multiple conditions.
This function appears to fly under the radar, yet is incredibly useful. The most well-known alternative to VLOOKUP is the INDEX and MATCH combination, but DGET should also be considered.
The greatest strength of DGET is the ability to handle multiple conditions. By using a criteria range, it is simple to enter multiple conditions.
It relies on the headers matching so can lack the flexibility of other formulas, but the simplicity is a plus.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
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 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'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.
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.
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.
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.
Never heard about such a super formula, thanks for sharing
My pleasure Siddharth.
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 😊
Like your style man, very cool and to the point, no faffin, 🙏🏽thanks
I appreciate that. Thank you.
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
Love Excel. Thanks for this. I didn't know about DGET
Thank you Karen.
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.
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 cool. You taught this old dog a new trick.
Thank you.
Cool, never heard of DGET. Excels library is vast. Thx for sharing the knowledge.
My pleasure Tahj.
Crystal Clear Sir. Thank You
You're welcome Panagiotis.
Never heard of this function. It's amazing;)
👍
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.
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.
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.
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
Great. Thank you and I cant wait to start using this formula.
Happy to help Ardi.
Nice. Much easier alternative to array formulas.
Yes, definitely
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.
holy cow, this is what I have been looking in excel, all this time!! thank you!
You're welcome Eriol.
Awesome.. Learned something new and valuable today.. Never heard and didn't knew it was so easy.. Thanks..
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
many thanks Alan, I did not know dget existed, a great tool and a great tutorial as always. Cheers Mohideen
Thanks Mohideen.
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 :(
Excellent video. Thank for sharing. Never heard of DGET.
Thank you David.
Absolutely brilliant
Thank you 😊
Really thank you s much sir.... Very usefull formula
You're very welcome. Thank you.
Solved a problem I was having with Vlookup! thanks!
Excellent
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
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.
Wow, amazing, I never used this before but i will definitely try this. Thumbs up
Excellent! Thank you Khalid.
can you give example with next button change data from table to cell every click next button?
All I have to say is WOW!! Thank you.
You are more than welcome Georgia.
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.
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.
Very good ... Thank you
Thank you Joeey.
Very good, I can see applications for this
Thanks Clive.
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.
Thanks for sharing this valuable information
You're very welcome.
If possible, I want to click "like" for 100 times!👍👍👍+++
Thank you Stephanie.
very useful video. great 👌👍
Thank you 😊
The 'how-to" aspect was great. The tutorial could have been halved without the continuous sales pitch for the use of DGET(). It IS useful but perhaps not the greatest thing since sliced bread...
ha ha fair point. There have been many great things between and since.
Nice sharing...… I did not knew about this secret function of excel
Excellent! You're welcome.
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.
Thanku today I have learnt new function, dget.
Awesome. Great to hear Datta.
Great tricks , thanks
Thank you.
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.
Can't believe I haven't used dget all this while and been using helper cells for multiple criteria
It is fabulous.
Amazing !! Never heard of this function before.
Thank you.
Hi how to deal when the lookup value is not in a column. Rather spread across a table
Love this formula, great video ✔
Thank you.
Fantastic job sir! Thanks. 👍
You're welcome Ashish. Thank you.
Please put all excel practiced files in the description.
Wow!!!!!!!!! I never knew this. Fab work sir. Thanks
Thank you Mike.
Very helpful. thanks.
No problem Maria.
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!
This is better than using Vlookup! Thanks for posting the tutorial!
You're very welcome.
Wonderful tutorial
Thank you Sam.
Cool man, another way of extracting data on multiple criteria
Thanks Jon.
You touched on how this would help if there were multiple values that could return, but you didn't delve into it. Arguably would have made this video a lot better, complete even.
Dget(NamedRange,FieldToReturn,Conditions)
The Conditions part is exactly like the where clause in SQL, so:
"Name = ""Jon"" And ID = 18"
or
"Name > ""a"" OR Price = 7"
would be perfectly valid.
Don't quote me on the quotes though. Haven't done this on a long time. Might be single quotes, might be no quotes. trial and error!
Very useful!!!
Thank you.
you deserved to be subscribed
Thank you Yosef.
@@Computergaga because you helped people. thank you too 👌(from the Philippines)
I have hardly ever used excel’s D functions. Since Power Query and Power Pivot were added to excel, even VLOOKUP is pretty redundant now. Good reminder of how DGET works though.
You're absolutely right, and with XLOOKUP here too there are many new and improved ways to do what we once relied on VLOOKUP for.
Awesome thanks for.nice presentation.
You're welcome. Thank you Gurrappa.
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.
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
Hi brother,
I need help with a case. Please let me know if you could help me with it.
Thanks,
Saqib
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.
just use combination of =MaTCh and =index its way more effective and flexible
Sir please make video on how to install and run office 2019 in window 8
can you make video on the following functions together for if(indirect(concatenate(match()))
Is there any options to hide formulas when i click in a cell containing it..
Excellent.
Thank you very much Khalid.
Can we use dget if the table is in a different workbook
Definetely thumb up for this video sir!
Thank you Miroslav.
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?
thanks for sharing....
My pleasure.
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?
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.
Would the database part become dynamic if it were made into a table that can be updated?
Thanks for sharing .
You're welcome Hemanth.
so much better than vlookup. ive been looking for this for years
Great Raphael.
try with multiple rows and reply how better then vlookup ?
Sir,
How we can get data from daybook, to create party wise ledger, with sales details and receipt details. Please help me
How can DGET detect exact match or approximate match?
Thanks
You're welcome Dumpala.
Great thank you!
My pleasure Kevin.
Superb! Thanks.
You're welcome Chris.
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 👍
Ver y useful....thx for sharing
You're very welcome Oliver.