DGET Function - The Secret VLOOKUP Alternative for Multiple Conditions
Тәжірибелік нұсқаулар және стиль
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.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
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
Пікірлер: 430
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!
@Computergaga
3 жыл бұрын
Happy to help 👍
I can make Excel sing with the best of them and I have never heard of DGET. Well done, mate!
@Computergaga
5 жыл бұрын
Thanks Doug. Excel has a beautiful voice.
@andrewlankford9634
5 жыл бұрын
Probably because new versions of Excel try so hard to hide the help file from the user.
Bravo. Thanks for showing me something new! I've been using Excel for years and never heard of DGET.
@Computergaga
5 жыл бұрын
You're welcome George. Thank you.
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.
@Computergaga
4 жыл бұрын
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.
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.
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!
@dougmphilly
5 жыл бұрын
seriously, that was so simple
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!
@Computergaga
6 жыл бұрын
Thanks Wayne.
Cool, never heard of DGET. Excels library is vast. Thx for sharing the knowledge.
@Computergaga
5 жыл бұрын
My pleasure Tahj.
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!
@Computergaga
5 жыл бұрын
My pleasure Luis.
It's fantastic. This function is a combination of some features of vlookup and advanced filter. thanks for sharing
@Computergaga
5 жыл бұрын
Thank you. You're welcome Sachin.
@rajeshmmishra5062
3 жыл бұрын
@@Computergaga .. hi it's working only for 1st row not for next
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.
@Computergaga
6 жыл бұрын
My pleasure Ahmed.
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!!! 👍
@Computergaga
4 жыл бұрын
Thank you.
holy cow, this is what I have been looking in excel, all this time!! thank you!
@Computergaga
5 жыл бұрын
You're welcome Eriol.
Great. Thank you and I cant wait to start using this formula.
@Computergaga
5 жыл бұрын
Happy to help Ardi.
Awesome.. Learned something new and valuable today.. Never heard and didn't knew it was so easy.. Thanks..
Never heard about such a super formula, thanks for sharing
@Computergaga
5 жыл бұрын
My pleasure Siddharth.
Love Excel. Thanks for this. I didn't know about DGET
@Computergaga
5 жыл бұрын
Thank you Karen.
many thanks Alan, I did not know dget existed, a great tool and a great tutorial as always. Cheers Mohideen
@Computergaga
6 жыл бұрын
Thanks Mohideen.
You do realise I've got to rewrite all my spreadsheets now! Really useful stuff!
@Computergaga
5 жыл бұрын
Sorry buddy 😀
@jbkend
4 жыл бұрын
Sounds fun! 😁
SUPERB!!! NEW ADDITIONAL KNOWLDGE this is really a Secret VLOOKUP alternative
@Computergaga
6 жыл бұрын
Thank you Jaipal.
@JPAlpano
5 жыл бұрын
Only works if you are only looking for one id. If you have multiple ids to lookup, you cannot drag the formula down.
All I have to say is WOW!! Thank you.
@Computergaga
6 жыл бұрын
You are more than welcome Georgia.
nice function gonna use it monday was doing it with a long index and match formula now thanks for the tip
@Computergaga
5 жыл бұрын
Happy to help Martijn.
Excellent video. Thank for sharing. Never heard of DGET.
@Computergaga
5 жыл бұрын
Thank you David.
Wow, amazing, I never used this before but i will definitely try this. Thumbs up
@Computergaga
4 жыл бұрын
Excellent! Thank you Khalid.
Amazing !! Never heard of this function before.
@Computergaga
4 жыл бұрын
Thank you.
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.
@emmadkareem5247
4 жыл бұрын
The major issue is with Excel defines the "Criteria" for DGET. It should have been a simple condition.
Wow!!!!!!!!! I never knew this. Fab work sir. Thanks
@Computergaga
5 жыл бұрын
Thank you Mike.
You’re amazing. Plus 1 subscriber 💪💪💪
@Computergaga
5 жыл бұрын
Thank you Pap.
This is better than using Vlookup! Thanks for posting the tutorial!
@Computergaga
5 жыл бұрын
You're very welcome.
Love this formula, great video ✔
@Computergaga
5 жыл бұрын
Thank you.
Exceptional... Thank you
@Computergaga
5 жыл бұрын
You're welcome Mukesh. Thank you.
🎉 Very good! I was vaguely aware of this formula but haven't used it. Until now!
@Computergaga
Жыл бұрын
Thank you 😊
Crystal Clear Sir. Thank You
@Computergaga
3 жыл бұрын
You're welcome Panagiotis.
Like your style man, very cool and to the point, no faffin, 🙏🏽thanks
@Computergaga
4 жыл бұрын
I appreciate that. Thank you.
Awesome! Thank you!👍👍
@Computergaga
5 жыл бұрын
You're welcome. Thank you Shashikant.
Very helpful. thanks.
@Computergaga
6 жыл бұрын
No problem Maria.
Great tricks , thanks
@Computergaga
6 жыл бұрын
Thank you.
Thanks for sharing this valuable information
@Computergaga
5 жыл бұрын
You're very welcome.
Fantastic job sir! Thanks. 👍
@Computergaga
5 жыл бұрын
You're welcome Ashish. Thank you.
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
@Computergaga
5 жыл бұрын
This is true. Was designed as a summary function rather than for lists.
@loriperezlp
4 жыл бұрын
Hi Steve, You can use "Xlookup" function for lists. This replaced "Vlookup" for me.
@Stepford
4 жыл бұрын
@@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!
@shanefield7339
4 жыл бұрын
@@loriperezlp oh can we? *as we all sit here waiting for xlookup* :)
Definetely thumb up for this video sir!
@Computergaga
4 жыл бұрын
Thank you Miroslav.
Thanks Alan may include this in my intermediate course 😉
@Computergaga
5 жыл бұрын
Thank you.
Nice sharing...… I did not knew about this secret function of excel
@Computergaga
4 жыл бұрын
Excellent! You're welcome.
Solved a problem I was having with Vlookup! thanks!
@Computergaga
4 жыл бұрын
Excellent
Great thank you!
@Computergaga
6 жыл бұрын
My pleasure Kevin.
Never heard of this function. It's amazing;)
@Computergaga
2 жыл бұрын
👍
Wow this is fantastic!
@Computergaga
5 жыл бұрын
Thank you Roger.
Absolutely brilliant
@Computergaga
2 жыл бұрын
Thank you 😊
thanks for sharing....
@Computergaga
6 жыл бұрын
My pleasure.
Superb! Awesome
@Computergaga
5 жыл бұрын
Thank you Kirandeep.
Superb! Thanks.
@Computergaga
5 жыл бұрын
You're welcome Chris.
Ver y useful....thx for sharing
@Computergaga
5 жыл бұрын
You're very welcome Oliver.
brillant. thanks
@Computergaga
6 жыл бұрын
You're welcome Theo.
Thanks for sharing .
@Computergaga
5 жыл бұрын
You're welcome Hemanth.
Thank you very much!
@Computergaga
5 жыл бұрын
My pleasure Marcelo.
Wonderful tutorial
@Computergaga
5 жыл бұрын
Thank you Sam.
Super. Thanks
@Computergaga
5 жыл бұрын
You're welcome Sudhir.
Very cool. You taught this old dog a new trick.
@Computergaga
5 жыл бұрын
Thank you.
Very good ... Thank you
@Computergaga
5 жыл бұрын
Thank you Joeey.
Really thank you s much sir.... Very usefull formula
@Computergaga
4 жыл бұрын
You're very welcome. Thank you.
Very good, I can see applications for this
@Computergaga
5 жыл бұрын
Thanks Clive.
Cool man, another way of extracting data on multiple criteria
@Computergaga
5 жыл бұрын
Thanks Jon.
very useful video. great 👌👍
@Computergaga
2 жыл бұрын
Thank you 😊
Nice. Much easier alternative to array formulas.
@Computergaga
4 жыл бұрын
Yes, definitely
bloody brilliant
@Computergaga
5 жыл бұрын
Thank you Alex.
Very useful!!!
@Computergaga
4 жыл бұрын
Thank you.
Helpful tutorial sir
@Computergaga
5 жыл бұрын
Thank you.
you got a new subscriber :)
@Computergaga
5 жыл бұрын
Excellent. Great to have you aboard.
Wow.... Thank u
@Computergaga
5 жыл бұрын
You're very welcome.
Wow!!! Thanks a lot.
@Computergaga
4 жыл бұрын
You're very welcome Eric.
Excellent.
@Computergaga
5 жыл бұрын
Thank you very much Khalid.
Thanks buddy
@Computergaga
5 жыл бұрын
You're very welcome Sredhar. Thank you.
Been using vlookup for a long time, always annoyed by the limitation. Thanks for the video, learned a better replacement for vlookup!
@patriktrivedi
5 жыл бұрын
dear its not like replacement. vlookup is far better then this one.
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.
Thanks
@Computergaga
5 жыл бұрын
You're welcome Dumpala.
Excellent
@Computergaga
5 жыл бұрын
Thank you Ravi.
Great video…
@Computergaga
6 жыл бұрын
Thanks Keval.
Wonderful
@Computergaga
5 жыл бұрын
Thank you.
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
It helps me so much as an excel warrior,
@Computergaga
5 жыл бұрын
Keep fighting the fight warrior
One word waaaaooo thanks sir
@Computergaga
5 жыл бұрын
You're welcome Rashpal.
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!
@Computergaga
5 жыл бұрын
Yes, they sure are useful for this Duncan.
Superb
@Computergaga
5 жыл бұрын
Thank you Babar.
Omg I'm using it today
@Computergaga
5 жыл бұрын
Awesome Cristina.
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
Wow!!!
sir very nice formula
@Computergaga
5 жыл бұрын
Thank you.
can you make video on the following functions together for if(indirect(concatenate(match()))
Thanks for clear explaination. It's really helpful. Instead of index and match, can i use dget function.
@Computergaga
5 жыл бұрын
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.
If possible, I want to click "like" for 100 times!👍👍👍+++
@Computergaga
6 жыл бұрын
Thank you Stephanie.
thanks
@Computergaga
5 жыл бұрын
My pleasure Frank.
great!
@Computergaga
5 жыл бұрын
Thank you Akhmet.
you deserved to be subscribed
@Computergaga
5 жыл бұрын
Thank you Yosef.
@yosef-yosef9414
5 жыл бұрын
@@Computergaga because you helped people. thank you too 👌(from the Philippines)
Thanku today I have learnt new function, dget.
@Computergaga
5 жыл бұрын
Awesome. Great to hear Datta.
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)
@clabzzz
5 жыл бұрын
SUMPRODUCT with text criteria is probably one of the most common functions I use in Excel. Very helpful!
@femmeNikita27
5 жыл бұрын
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.
@JBalshaw22
5 жыл бұрын
@@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?
@femmeNikita27
5 жыл бұрын
@@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.
@JBalshaw22
5 жыл бұрын
@@femmeNikita27 You're very welcome, let me know how you get on.
I use index-match all the time to match by row and/or column. This DGET function looks to be useful, thanks.
@Computergaga
4 жыл бұрын
You're welcome Stan.
TNX BROTHER
@Computergaga
5 жыл бұрын
You're welcome Mahaboob.
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?
@Computergaga
5 жыл бұрын
I'm afraid not Tochukwu. This lookup is more for your summary uses.
so much better than vlookup. ive been looking for this for years
@Computergaga
5 жыл бұрын
Great Raphael.
@patriktrivedi
5 жыл бұрын
try with multiple rows and reply how better then vlookup ?