Excel DGET Function Solves 2 of Your VLOOKUP Problems
HTML-код
- Опубликовано: 4 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
It's time to explore an underutilized yet powerful formula: the DGET function. Perfect for solving complex LOOKUP problems that VLOOKUP can't handle, DGET allows for searching based on multiple conditions and looking up values to the left of the LOOKUP range.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/dget-file
✨ Key Highlights:
▪️ Solving Multiple Condition Lookups: Learn how DGET overcomes VLOOKUP limitations by enabling searches based on multiple criteria.
▪️ Left Lookups: Discover how to use DGET for retrieving values to the left of the LOOKUP range, a task challenging for traditional LOOKUP functions.
▪️ Practical Examples: Follow real-world scenarios to understand how to implement DGET in various situations, like finding division and department names based on specific criteria.
▪️ Dynamic and Flexible Filtering: See how DGET can dynamically filter data and adapt to approximate matches for versatile data analysis.
▪️ Understanding Limitations: Get to know the potential drawbacks of DGET, such as handling duplicates and limitations in applying the formula to multiple cells.
Excel DGET Function is one of Excel's "forgotten" database functions that can do a Left lookup (unlike VLOOKUP) and ALSO easily look for multiple criteria.
One limitation of DGET is that you can't apply it to multiple cells. Basically you can't pull down the formula to lookup different values. If that's something you need, then you'll need to use INDEX MATCH, VLOOKUP, Excel FILTER function or FILTER feature or XLOOKUP. Check out the links under Related Videos below to find these videos.
00:00 Excel DGET Function
01:44 Excel DGET Explained
06:03 DGET With Approximate Match
06:28 DGET With OR & AND Conditions
08:18 DGET With Multiple Criteria
Excel DGET disadvantages:
1. DGET formula can't be pulled down - you'll need to include the header for each lookup value
2. DGET returns an error (#Num error - 7:25) if there are duplicate lookup values in the range (as opposed to VLOOKUP or Index & Match which return the first match).
----- Related Videos ------
Excel Index Match basics: • How to use Excel Index...
Excel VLOOKUP explained: • VLOOKUP EXPLAINED - 2 ...
Excel XLOOKUP: • How to Use the NEW & I...
Excel FILTER function: • Excel FILTER Function ...
Check out the complete Excel Lookup Formula Playlist: • Excel Lookup Formulas
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/dget-file
Thanks Leila. Most enjoyable. Sometimes the old formulas need an introduction to a modern audience. Love to see what you can do with the "N" Formula :-)
I really enjoy your excel tutorials. I am two years retired from a job where I used excel extensively, but I still enjoy writing formulas for personal spreadsheets. Thank you for sharing you excel skills.
As always a very useful and cristal clear video, many thanks !
Another option for multiple criteria is to use Xlookup with concatenate criteria and lookup on concatenate columns. It works well but request more memory to calculate the results (particularly when calculating on a big dataset)
Hi Leila. Thanks for the great DGET formula examples! Good to know how this function works :)) Thumbs up!!
Thanks for covering DGET. Ostensibly it works like LOOKUP function but far more easy to follow. Thanks again:)
My pleasure Sachin :)
Very useful video, your solutions give a professional touch to each worksheet
Leila, thank you so much for being such an amazing teacher of Excel. If I knew just 1% of what you know as well as you know it I would know a thousand times more about Excel than I do. Thank you again and again.
Thanks Leila for this amazing function, will definitely use it
Wow, thanks for inspiring and solving an issue I had just last week. Can't wait to get to work and implement this!
Hope it will be useful.
Don't think I stop watching your videos, DGET, this function is so magical 😍
Oh, there you are. I thought you had forgotten me :)
It's great. I was looking for such videos. Now I can solve so many problems. Thanks Leila.
All your videos worth watching and thumbs up.
Thank you.
Thank you very much Leila for showing this forgotten function, it is very useful and compatible with old versions of Excel :)
My pleasure. Glad it's useful.
Great. I have decided not to miss your new video tutorial....because it's of great help in my daily routine/work. Many thanks Ms. Leila G.
Very good decision :) Glad the tutorials are helpful.
Thank you so much @Leila, You are the best :-)
Always looking forward to learning something new in excel
Clean and simple for us beginners, thank you.
Great video as always. Thank you very much for your time and knowledge.
Thanks Leila. An oldie but a goodie.
Nice addition for the index and match combo funtion is that match() works for multiple matche per row too.
You could youse the multiple matches like (A1="x")*(B1="x") etc.
Excel could take up some long processing time with large tables though.
Mine took around 30 to 40 minutes for a table with 7000 rows and 3 matches acros.
Hi Leila! I like your excel videos ....alot.. I have learnt many things because of you... thanks alot
you had long time to publish this video I appreciate that. Thanks that was wonderful mum..
Respect
Glad you like it.
شكراً!
!This looks like it could be a bit easier for me to use than index/match in some situations! Thanks again
This is great, You present it very nicely. Thank you!
@Leila I see you bring an old, weel-worn excel to life = good job, merci :)
Gotta respect the Goldies :)
Very well explained, as always! Thanks Leah . . .
Thx Leila. Very clear. I see the #num as both a con as you say and a also as a pro, as getting the first occurrence of duplicates not knowing there are others can lead to errors in interpretation and decisions.
Thanks. Following your videos and I am learning so much.
I love you please never stop making videos
Thank you for the great function. just to make something right. I have tried using the function with a table instead of range selection and it is not working. the reason that I figured out is that typing the table name makes it selected without the headers. in order to use it as a full table, you need to refer it as =DGET(Table1[#All],1,J4:J5)
This is great function! Thanks for showing it to us Leila! I will no longer need to count columns for Vlookup in my files with 200 columns! I use Index-Match often to avoid it, and Dget seems to be even more elegant solution for many situations.
problem is you can only lookup 1 row with DGET, so you will still have to use index match
You don't need to count columns for vlookup. It shows the current column count in the tooltip at the lower right part of your selection.
Just what I was looking for, thanks
Learning a lot from your tutorials. Easy to understand...Way to go...
That's great. Thanks for the feedback.
I don't know about this formula. Thank u so much for increasing my knowledge
Thank you for the tutorial. Very informative.
Quite informative as always, thanks! 😃👌
Good stuff. Really like your channel!
This is the first time i know about this function, thankss 😊
We've been waiting for this. But still a fan of Index-Match. 😁
Both have their place :)
Same here
Ditto, but this was way cool
True. Didn't even find a case to use this with justice nor does this seems to offer any
I'm using & for complex criteria.
That's a wonderful function, but I feel, LOOKUP is still a better alternative. You always ROCK Leila :)
Thank you. For all your support
You are the best woman in the world.
Thank you very much, Leila! I congratulate you for your high quality in the transmission of knowledge. By the way, I've tried DGET with Tables, and found to my amazement and sadness that it doesn't work.
Wonderful as always. thanks
Awesome, i always google when it comes to Excel formulas :D
Great explanantion!
Nice laptop by the way (for a Office user geek xD)
Haha. Gamer laptops are great for video editing too :)
Thanks for given your contribution
Thanks again. You are wonderful. Great job.
going old school! Thanks for the DGET example :-)
Totally! Bringing out the classics :)
best teacher..............and teaching style
Great and very useful information...thanks for knowledge sharing....
Very good tutorial as always! :)
Awesome video... Easy to understand.
Great video, thanks!
Thank you for this video Leila.
Ur really great... Iam 13 yrs working has wfm... I never came across such a easy and important formula
Yours technique way is simple and easy. User friendly.
Because of you today i able to learn new funcation which i dont know before thanks
I salute your teaching skill...
Thank you so much for such amazing video 😊
Your teaching of excel is really lovely and impacting. Please I want you to produce a video that will teach how to prepare school time table that will be high flexible.
With the following features:
1. A teacher possibly taking more than 1 subject.
2. A class possibly having 2 subjects at the same periods.
3. Each subject having its own no of periods per week.
4. Possibility of science subjects in science laboratory, with consideration that two/three classes may be sharing the same laboratory.
And other possible features.
I will appreciate it if you can work on it. Thanks.
Excellent explanation.
Thanks, nice concept
Really Nice look..and nice teaching .
I wonder if we can create a "Four-way logarithmic graph" in excel (the 2 vertical and horizontal axis plus 2 extra diagonal axis). I might be asking too much
Nah
You are a great professor Leila I love your channel!.Thank you for the excellent tip.^_^
Thank you! 😃
Very useful tool...Thanks
You are an amazing teacher
Great video! Thanks a lot! it was usefull!
I like Dget function, it is very handy. Vielen Dank Liebe Leila, Viele Liebe Grüsse 🤗
It does have its benefits. Glad you like Katerina :)
Never used DGET before. I'll definitely have to experiment with it more.
You teach so good
Thank you so much from Tehran
Genius! Hands down genius.
Awesome. No more working with array formulas. Thanks
I love all ur videos
I was in a rush and last time i did this i did a 6 way repeating if statement. Thanks for sharing its much simpler this way than that and INDEX matching :D
Glad it's helpful. DGET definitely has its advantages in certain situations.
Thank you Leila!
Leila, I appreciate your informative videos, but I am having limited success using DGET() to fetch a single value within a table based on two values in the same table from another sheet. Essentially, only the first one works because the heading is on row 1 and the criteria is on row 2, but when I try to "knit" together a range that assembles the heading on row 1 with the values on row 3, it fails to fetch the value from the other sheet. Any thoughts on that one? Thanks!
You are the best. I will like to open up a centre in Cameroon named after you
Brilliant. I never knew that.
Another great video. I think it would be better for database to use Offset function. That way it is dynamic. For list it could be use Sort, Offset and Unique combination but not everbody has Unique function.
Good knowledge for me!
Great video. So other than xlookup, what do we use for drill down for multiple conditions?
Awesome function
with DGET function we are able to find just one raw multiple match. Can we use it to find other values for two or three rows?
is there any possiblity if we look up rows like Hlookup?
Thanks Leila for this tutorial.👍👌
Great Video Leila. Very Interesting. :) :)
I was delighted to watch your Excel lesson. You grabbed my full attention
Happy new year Leila!⛄️
Same to you Vijay!
I'll have to use this once I DGET an opportunity.
Woo amazing... thank you so much Mam....
Interesting!
Gonna test it soonish. I wonder if the criteria can be made from some composite range maybe with index((...,...),0) around it. Maybe it was {...,...} to append ranged? If that works you can have separate headers and values in formula.
Since I'm using Power BI a lot I recommend turning everything that is a table into actual tables. Power Query is so nice to get from raw data source to proper data, might be good practice so all steps are repeatable.
Looking back excel it seems very error prone and encourages complex tinkering that isn't easy to document or for colleagues to use. Using queries and the data model might help.
Very useful formula
nice information this thank you very much ,,,,,,
Good explanation
Very useful, in certain situations. Thanks for not forgetting us non 365 users.
You'll definitely not be forgotten :)
"You're simply the best, better than all the rest
Better than anyone, anyone I ever met"
Gotta love Tina Turner!
Gotta love cff/sbb clock 🤣
Marvelous work madam...
Hi Leila,
In my view, in the presence of match , index function, which gives much more flexibility then Vlookup. Then why we don't shift Match, Index function.?
Thanks
Fantastic..thq
Nice lesson!
Thanks! 😃
Thanks Leila :)