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.
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.
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)
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.
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.
The error at 8:17 makes perfect sense as one person would be assigned multiple departments or divisions, and which resulting values should the DGET function then return? The error lies in the representation of the relations in the data table. The good, old "crap in, crap out" principle.
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.
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 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
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.
I know DGET for almost 15 years now and is an excellent function but I've never used it because of the problem of copying down. I watched your video hoping that you could find a solution. Excellent video as usual. Apart from DGET there are also other database functions, such as DSUM.
@@fabianstra2155 doesn't work, since then it would see ALL the cells between the header and the current as part of the criteria... What might work is to just have every odd-number row for the headers (and hidden) and then even-number rows for the values. Then it would allow for copying down, we just need to hide every other row... :-|
Vlookup is a simple instrument. So, you can use it with additional collumns where you can point your criteria which you can rule in separated cell. I've began to do this couple years ago when tasks began to grow and vlookup helped me very well.
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.
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.
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)
4:00 You could also reference A2, which already contains the fieldname. But putting the fieldname directly above DGET gives visual indicator of what field the formula is returning, which you might or might not want in your application.
Grab the file I used in the video from here 👉 pages.xelplus.com/dget-file
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.
best teacher..............and teaching style
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.
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)
Thanks for covering DGET. Ostensibly it works like LOOKUP function but far more easy to follow. Thanks again:)
My pleasure Sachin :)
Thanks Leila. An oldie but a goodie.
Ur really great... Iam 13 yrs working has wfm... I never came across such a easy and important formula
It's great. I was looking for such videos. Now I can solve so many problems. Thanks Leila.
Learning a lot from your tutorials. Easy to understand...Way to go...
That's great. Thanks for the feedback.
Hi Leila. Thanks for the great DGET formula examples! Good to know how this function works :)) Thumbs up!!
Hi Leila! I like your excel videos ....alot.. I have learnt many things because of you... thanks alot
All your videos worth watching and thumbs up.
Thank you.
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.
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.
I love you please never stop making videos
Very useful video, your solutions give a professional touch to each worksheet
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.
Don't think I stop watching your videos, DGET, this function is so magical 😍
Oh, there you are. I thought you had forgotten me :)
The error at 8:17 makes perfect sense as one person would be assigned multiple departments or divisions, and which resulting values should the DGET function then return? The error lies in the representation of the relations in the data table. The good, old "crap in, crap out" principle.
I salute your teaching skill...
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.
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.
Thanks, nice concept
Really Nice look..and nice teaching .
Excellent explanation.
Great and very useful information...thanks for knowledge sharing....
You teach so good
Thank you so much from Tehran
Thanks. Following your videos and I am learning so much.
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.
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 :-)
Yours technique way is simple and easy. User friendly.
Great video as always. Thank you very much for your time and knowledge.
Thanks for given your contribution
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.
@Leila I see you bring an old, weel-worn excel to life = good job, merci :)
Gotta respect the Goldies :)
Genius! Hands down genius.
Good explanation
Just what I was looking for, thanks
Because of you today i able to learn new funcation which i dont know before thanks
Marvelous work madam...
Love from Pakistan.. doing great.
Awesome video... Easy to understand.
Good stuff. Really like your channel!
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.
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.
Thank you for this video Leila.
Thank you Leila!
Nice lesson!
Thanks! 😃
Very useful formula
Very useful tool...Thanks
Thanks Leila for this amazing function, will definitely use it
Great video, thanks!
Thank you. For all your support
You are the best woman in the world.
Awesome. No more working with array formulas. Thanks
Hi Leila!DGET Seems An Intresting Function!I've Never Really Tried It But After This Tutorial I will Definetly Give It A Try!Thank You :):):)
It definitely has its advantages. Hope it will come in handy.
Love from india. U r really great
Good knowledge for me!
going old school! Thanks for the DGET example :-)
Totally! Bringing out the classics :)
I know DGET for almost 15 years now and is an excellent function but I've never used it because of the problem of copying down. I watched your video hoping that you could find a solution. Excellent video as usual. Apart from DGET there are also other database functions, such as DSUM.
Aggregate the fixed titel-cell with a not fixed row cell in the formula
@@fabianstra2155 doesn't work, since then it would see ALL the cells between the header and the current as part of the criteria...
What might work is to just have every odd-number row for the headers (and hidden) and then even-number rows for the values. Then it would allow for copying down, we just need to hide every other row... :-|
شكراً!
!This looks like it could be a bit easier for me to use than index/match in some situations! Thanks again
Never used DGET before. I'll definitely have to experiment with it more.
Very useful. Thank you
Nice Video Mam
Clean and simple for us beginners, thank you.
Awesome function
Thanks again. You are wonderful. Great job.
Vlookup is a simple instrument. So, you can use it with additional collumns where you can point your criteria which you can rule in separated cell.
I've began to do this couple years ago when tasks began to grow and vlookup helped me very well.
Thanks for sharing your experience, John!
@@LeilaGharani the old school is ruling. :) Thank you, Leyla, for your videos. Have a nice year!
Great explanantion!
Nice laptop by the way (for a Office user geek xD)
Haha. Gamer laptops are great for video editing too :)
This is great, You present it very nicely. Thank you!
Thank you very much 😍😍😘😘
Very well explained, as always! Thanks Leah . . .
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.
so many thanks ❤❤
You're welcome 😊
I love all ur videos
This is the first time i know about this function, thankss 😊
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 :)
Really great 😍😍😍
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 🤣
Thank you so much for such amazing video 😊
you had long time to publish this video I appreciate that. Thanks that was wonderful mum..
Respect
Glad you like it.
Thanks dear
Happy new year Leila!⛄️
Same to you Vijay!
Thanks for sharing
Awesome, i always google when it comes to Excel formulas :D
Great video! Thanks a lot! it was usefull!
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.
Brilliant. I never knew that.
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)
4:00 You could also reference A2, which already contains the fieldname. But putting the fieldname directly above DGET gives visual indicator of what field the formula is returning, which you might or might not want in your application.
Thank you for the tutorial. Very informative.
Nice video and can you make a video on how to automatically update the formula without dragging or copy paste.
You are an amazing teacher
nice information this thank you very much ,,,,,,
Thanks Leila :)
Fantastic..thq
Thank you.
A big 👍. Thanks a lot
Most welcome 😊
Nice video, could u please make videos on Power BI