Hi Kenji! There is also an easy way of dragging an xlookup function in your example: XLOOKUP(; ; Table[range]) can be changed to: XLOOKUP(; ; Table["&&"]) That makes XLOOKUP more dynamic
Thank you so much, Kenji! You really make everything related to Excel easier! Been binge-watching all of your videos. Once again, thank you so much! Keep up good work!
12:04 is the big killer for DGET for most of the situations where you would use XLOOKUP and/or INDEX/MATCH. DGET is for managers...not for data cleaning, mining and stats. The one line example is designed to advantage DGET.
Hi Kenji, firstly thank you very much for the clear walkthrough. It’s helpful. I would like to enquire if there’s any function that DGET work with, such as the same from XLOOKUP with multiple criteria? Thank you!
Hi Jack, there is indeed a way. XLOOKUP makes a set of TRUE and FALSE statements in the range where it looks for the value, so you can do it one step ahead: if you have a range1 and a range2, with criteria1 and criteria2, you would write it like this: XLOOKUP(1; (range1 = criteria1)*(range2 = criteria2); ) Whenever there is a match in both criteria, TRUE times TRUE equals 1, so the XLOOKUP will find a match where there is the double match. Nevertheless, DGET has a much intuitive way of looking with multiple criteria though, by changing the size of the last parameter. Does that help?
Hello sir, I am looking for a lookup function that can look through a table of multiple columns and return data in the last column when data on any of the 1st, 2nd, or 4rd column is looked up
thank you so much for sharing! this will definitely make my formulas so handy! quick question, say I bought the course for ‘Excel for Business and Finance’ months back, do you update these whenever you discover new techniques and will I have accessibility to it?
Thank you! Yes the course gets updated often (last update was last month to add new Excel features and improve lesson delivery) you can reach out to info@careerprinciples.com if you have any other doubts!
Kenji, for the DGET vs XLOOKUP, could you not do a nested XLOOKUP where return value is a second criterion to filter for the month? Although you can do this in XLOOKUP, your logic still stands that DGET is simpler, just clarifying that you could have done that in the example you provided.
I'd have to check but I think what you propose would work! As you mention, the DGET might be easier to do (and for others to read/understand) vs a nested XLOOKUP
Can you advise the formula for dates? Let's imagine the case when the car X was provide with a pass Y for some period (the pass was issued on May 4, 2023 to June 4, 2023). But I know that the same car was visiting some place on June 7, 2023, i. e. without any pass. How do I check it by formula? Thanks
@@volvo945 XLOOKUP is a natural array formula, so if you have multiple criteria it has to iterate through EVERY permutation for EACH ROW which is computationally intensive. A good alternative (if you do indeed need multiple criteria) is to create a concatenated column in both your destination and lookup tables and XLOOKUP on those. It's obviously not going to work for every scenario, but I've found it to be significantly more performant. If not, use power query!
Interesting to test, though id imagine since its parsing the entire table rather than just the relevant columns, it would be slower than xlookup for larger datasets.
Hi Kenji, I was just working on a project that basically requires me to do this, but the other way around. For example; I have a number of 2,036, then my output should be Switzerland. This required me to make a string with =vlookup and string it together by using (if not found ~> vlookup).. it’s a large document, so I strung it together 50 times.. Making my file extremely slow. Any tips?
About the last limit... You can write a dynamic formula with Idirect&Address&Match. then... You can make it dynamic But I found all those "D-functions" useless. Every action there's a simpler way...
Ehh. Having to organize the criteria that way makes the DGET function wayy more trouble than it's worth to me. Would rather just use custom lambdas wrapping over XMATCH/XLOOKUP/FILTER functions as needed.
👉 Take our Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance
Ok
Hi Kenji! There is also an easy way of dragging an xlookup function in your example:
XLOOKUP(; ; Table[range]) can be changed to:
XLOOKUP(; ; Table["&&"])
That makes XLOOKUP more dynamic
Needed this a lot, saved me at work, but my PC isn't working very well, I might need to get a new PC with or a good Office... driving me CRAZY
Girl, there is a good one called BNH Software, but you might need to fix that PC, probably other thing, don't go "CRAZY" ;)
I got mine from them as well, for PC perhaps a Ryzen 5 with good RAM, Office needs a good PC, especially for work
Nice videos
Trick for you: try using the arrows when you hover the header or first column lines: one click for data next click for header included.
Sir, please make a video on Power Pivot, Data Modeling, DAX, & Relationships.
Yeah yeah 👍
Excellent Kenji , easy explanation , also you teach limitations of formula
Thanks a lot
Thank you 🙏
Thank you so much, Kenji! You really make everything related to Excel easier! Been binge-watching all of your videos. Once again, thank you so much! Keep up good work!
Thank you!
Your the Best,Kenji i just try to listen to others ,how they teach on their chanells ....Your a Good teacher...😊
I appreciate that!
Wow this really great. Never heard of DGET before. I was curious seeing the notification. Thank you for the video❤.
Glad it was helpful!
12:04 is the big killer for DGET for most of the situations where you would use XLOOKUP and/or INDEX/MATCH. DGET is for managers...not for data cleaning, mining and stats. The one line example is designed to advantage DGET.
Hi Kenji, firstly thank you very much for the clear walkthrough. It’s helpful. I would like to enquire if there’s any function that DGET work with, such as the same from XLOOKUP with multiple criteria? Thank you!
Hi Jack, there is indeed a way. XLOOKUP makes a set of TRUE and FALSE statements in the range where it looks for the value, so you can do it one step ahead: if you have a range1 and a range2, with criteria1 and criteria2, you would write it like this:
XLOOKUP(1; (range1 = criteria1)*(range2 = criteria2); )
Whenever there is a match in both criteria, TRUE times TRUE equals 1, so the XLOOKUP will find a match where there is the double match. Nevertheless, DGET has a much intuitive way of looking with multiple criteria though, by changing the size of the last parameter.
Does that help?
Hello sir, I am looking for a lookup function that can look through a table of multiple columns and return data in the last column when data on any of the 1st, 2nd, or 4rd column is looked up
Thnkx does it also find fuzzy lookup?
Are there any offers for Black Friday sale as it is expensive right now
Can we use dget to pull information from different file
Thank You! Excellent content!!!
been using excel for a while but first time i hear this formula
it's a nice alternative!
Thank you Kenji. Didn't know about DGET.
Thanks for watching :)
Joder Kenji.. que buen truco esa funcion de DGET... La voy a comenzar a usar de nuevo.. Gracias
Muchas gracias! Un saludo 👍
Very Thankfull to you ❤
Excellent
Please make videos on Power BI and Power Query
Very useful. Thanks Kenji 😊
brilliant video once again! will be purchasing your business guide for sure thanks
Awesome, thank you! Liverpool fan I see you XD
Please make a video about finding names and gender by showing resume. Thank you!
Thank you for your video, it was very helpful
Had forgotten all about Dget and did not realise it was dynamic which is one of the drawbacks the others.
Yes!
thank you so much for sharing! this will definitely make my formulas so handy! quick question, say I bought the course for ‘Excel for Business and Finance’ months back, do you update these whenever you discover new techniques and will I have accessibility to it?
Thank you! Yes the course gets updated often (last update was last month to add new Excel features and improve lesson delivery) you can reach out to info@careerprinciples.com if you have any other doubts!
great video didnt know about DGET thanks
Great video Kenji
Which Excel is it? 2019 or later?
Kenji, for the DGET vs XLOOKUP, could you not do a nested XLOOKUP where return value is a second criterion to filter for the month? Although you can do this in XLOOKUP, your logic still stands that DGET is simpler, just clarifying that you could have done that in the example you provided.
I'd have to check but I think what you propose would work! As you mention, the DGET might be easier to do (and for others to read/understand) vs a nested XLOOKUP
xlookup is my favourite. Now dget to be checked
Really interesting uses!!
thks, it helped!
Can you advise the formula for dates? Let's imagine the case when the car X was provide with a pass Y for some period (the pass was issued on May 4, 2023 to June 4, 2023). But I know that the same car was visiting some place on June 7, 2023, i. e. without any pass. How do I check it by formula? Thanks
hey i made a whole video on date functions here: th-cam.com/video/9FTiTwTC6D4/w-d-xo.html
Is DGET any better peformance wise? My XLOOKUPs are struggling with the amount of data i am analysing
100% agree. I have some XLOOKUP formulas taking 10+ minutes to calculate.
Interesting, I haven’t tested that but could be worth a shot!
@@volvo945 time to change your infra.
@@volvo945 XLOOKUP is a natural array formula, so if you have multiple criteria it has to iterate through EVERY permutation for EACH ROW which is computationally intensive. A good alternative (if you do indeed need multiple criteria) is to create a concatenated column in both your destination and lookup tables and XLOOKUP on those. It's obviously not going to work for every scenario, but I've found it to be significantly more performant.
If not, use power query!
Interesting to test, though id imagine since its parsing the entire table rather than just the relevant columns, it would be slower than xlookup for larger datasets.
Hi Kenji, I was just working on a project that basically requires me to do this, but the other way around. For example; I have a number of 2,036, then my output should be Switzerland. This required me to make a string with =vlookup and string it together by using (if not found ~> vlookup).. it’s a large document, so I strung it together 50 times.. Making my file extremely slow. Any tips?
This is cool. Thanks
Great video ❤
Thank you!
how abt mutiple values match
7:46 Those driver names 😂😂
haha glad you noticed XD
Can you share pdf of 101 ways to master in pivot tables
POLAND MENTIONED!!!!
Let’s go! Haha
Brilliant but i can't use this as my excel is 2016.... But thanks anyway...
I’m going to try DGET to replace some of my xlookup functions. XLOOKUP is a core hog if you’re doing AND type logic on large tables.
if i put in "dget wildcard feature" the surname then dget doesn't work....
alternative filter formula?
If a column has more than one matching value, DGET will generate an error. VLOOKUP will produce the first match.
Lovely!
Thank you!
Hey in my excel when I do the same with excel data it showing me number errors and - #NUM like this
in 4:06, what happens if you typed "Spain"? In my case, when I choose the first name on the table excel returns a #NUM error.
Awesome
My idol😊
Thanks for watching :)
About the last limit...
You can write a dynamic formula with Idirect&Address&Match.
then... You can make it dynamic
But I found all those "D-functions" useless. Every action there's a simpler way...
Discount on course
Excel and Power BI
🎉
Thank you!
Limitations are soo big 😂
First
Nice!
Huh?
Ehh. Having to organize the criteria that way makes the DGET function wayy more trouble than it's worth to me. Would rather just use custom lambdas wrapping over XMATCH/XLOOKUP/FILTER functions as needed.
So… A dget function is like a glorified filter? 😂