I had moved from INDEX and MATCH, to VLOOKUP, and now to DGET. DGET really suits my needs as I am often needing to pull data from my database in sparodic, unstructured locations. It looks for the row/column headers as strings by default and not row/column numbers, which means I can specify the header inside quotes when a cell reference isn't handy. That is really useful for legibility as I can look at the cell and know precisely what it is for. I created a custom function which includes my database so I only have to specify the row and column. E.G. MYFUNCTION("April","East"). Very legible :) Thanks for the informative video.
Well, My GOTO functions now are XLOOKUP AND FILTER -- you said we can't vote for FILTER, too bad cause that is what I use all the time when returning a range of data. So, my vote is for XLOOKUP. It's built for the new dynamic arrays used in Office 365. Keep up the MAGNIFICENT work, all your videos are so informative and even show some really cool stuff you can do with functions you already know how to use, but you put them on the next level.
Good question! There is no function to count how many helper functions there are. But I have an if function that shows "One" or "Two" if the cell where I type the function is not Blank
Excellent tutorial. I've been following your tutorials for some time now Nabil, and you always say 'The best is yet to come'. I believe we have arrived at 'the best one'. My favourite function here is the 'not very well known' DGET function. BUT, in your 1st example with the VLOOKUP can I also do this =VLOOKUP($G$5,$A$2:$E$13,{2,3,4,5},FALSE) I learnt this one from Oz du Soleil. All the best.
Hi Rafael, and thanks for watching. DGET is a simple yet powerful function. NOT YET... We didn't arrive to the BEST :-) Thank you for sharing your solution and it's good if (and only IF) columns are in the same order as the source. by the way, I created an advanced tutorial (over a year ago) named: "Filter Non-Adjacent Columns" where I used the trick you mentioned. Then, I took it to the NEXT LEVEL. here is the link ua-cam.com/video/vCXunLFZecg/v-deo.html I am happy to have you as a subscriber.
An important note to remember when using DGET. When using DGET, make sure the dataset/Database has a primary key or unique key and your DGET criteria value is a value from the primary key or unique key column of the dataset/database. That is a value that is unique or not duplicated within its column in the database/dataset. Only then can this work without errors. Otherwise, you will get a #NUM! Error.
I could have done that but I wanted to link it to the Region (Column Header) and I explained further at 9:25 BTW, that's what I did for the Filter function and it is not linked to the region.
Why Exclude FILTER from the competition? Since it was introduced it has been my goto in this type of situation. But, among the other functions, XLOOKUP easily wins. Since DA functions became available I've used only FILTER, XLOOKUP, LOOKUP and XMATCH either alone or in combination with FILTER and ISNUMER depending on the setup. I also have used FILTER with CHOOSECOLS. It seems that the more complex the lookuo situation the more I seem to rely on FILTER.
Thank you Richard for the sharing your preference. The reason I excluded the FILTER is because none of the others stands the competition :-) What I tried to do, is to show everyone, the possible options in a way that those who do not have access to Dynamic Arrays, can still do the task. Of course, the functions you use are excellent.
I had moved from INDEX and MATCH, to VLOOKUP, and now to DGET.
DGET really suits my needs as I am often needing to pull data from my database in sparodic, unstructured locations. It looks for the row/column headers as strings by default and not row/column numbers, which means I can specify the header inside quotes when a cell reference isn't handy. That is really useful for legibility as I can look at the cell and know precisely what it is for.
I created a custom function which includes my database so I only have to specify the row and column. E.G. MYFUNCTION("April","East"). Very legible :)
Thanks for the informative video.
Thank u Nabil for this great video. I would go for Dget function
Very good! *Xlookup* . Thank you Nabil!
Thanks for watching Luciano. XLOOKUP is great
Goos comparison of the powerful functions. I like DGET function
me too
Thanks for watching
Well, My GOTO functions now are XLOOKUP AND FILTER -- you said we can't vote for FILTER, too bad cause that is what I use all the time when returning a range of data. So, my vote is for XLOOKUP. It's built for the new dynamic arrays used in Office 365. Keep up the MAGNIFICENT work, all your videos are so informative and even show some really cool stuff you can do with functions you already know how to use, but you put them on the next level.
Thank you so much Eric for the motivating comment
Excellent tutorial. A great comparison between these powerful functions!! Thank you my friend!!!
Thanks for watching Ivan, But, Which function did you select?
@@Officeinstructor FILTER. Why? Because it generates spilled-array. I also like DGET!! ;)
super duper tips 👌👌👌
Great work, more progress and prosperity, with sincere thanks, appreciation and respect
Thanks for watching Saad, but, which function did you select?
Filter
Great review. Thank you. Q: What is the equation you are using to evaluate the number of helper functions being used?
Good question!
There is no function to count how many helper functions there are. But I have an if function that shows "One" or "Two" if the cell where I type the function is not Blank
Excellent tutorial. I've been following your tutorials for some time now Nabil, and you always say 'The best is yet to come'. I believe we have arrived at 'the best one'. My favourite function here is the 'not very well known' DGET function.
BUT, in your 1st example with the VLOOKUP can I also do this =VLOOKUP($G$5,$A$2:$E$13,{2,3,4,5},FALSE)
I learnt this one from Oz du Soleil.
All the best.
Hi Rafael, and thanks for watching.
DGET is a simple yet powerful function.
NOT YET... We didn't arrive to the BEST :-)
Thank you for sharing your solution and it's good if (and only IF) columns are in the same order as the source. by the way, I created an advanced tutorial (over a year ago) named: "Filter Non-Adjacent Columns" where I used the trick you mentioned. Then, I took it to the NEXT LEVEL. here is the link ua-cam.com/video/vCXunLFZecg/v-deo.html
I am happy to have you as a subscriber.
An important note to remember when using DGET. When using DGET, make sure the dataset/Database has a primary key or unique key and your DGET criteria value is a value from the primary key or unique key column of the dataset/database. That is a value that is unique or not duplicated within its column in the database/dataset. Only then can this work without errors. Otherwise, you will get a #NUM! Error.
Really thanks a lot
Most welcome Vipul.
Which function do you choose?
@@Officeinstructor will be start using DGET from now onwards
Dear Nabil,
Why use the nested XLOOKUP function (04:25) when you can use it alone with all the results spilled?
=XLOOKUP(G4;A2:A13;B2:E13) 🤗
I could have done that but I wanted to link it to the Region (Column Header) and I explained further at 9:25
BTW, that's what I did for the Filter function and it is not linked to the region.
Never used dget. Thank you for the idea. Filter is the winner 🥇👍
It really is!
I usually use index
INDEX is great. Thanks for watching
I vote for DGET function - short and awesome. When was this function introduced?
Honestly, I love this function. it was introduced in 2003.
Thanks for watching Hashimu
👍👍👍 my vote for Index,Match
due to availability in all versions
Otherwise Xlookup
Thanks for watching and sharing your preference
Is DGET less popular?
If yes, why?
yes it's less popular. I don't see many people using it although it's a simple yet powerful function.
X-Lookup
Good choice Ahmed
Why Exclude FILTER from the competition? Since it was introduced it has been my goto in this type of situation. But, among the other functions, XLOOKUP easily wins. Since DA functions became available I've used only FILTER, XLOOKUP, LOOKUP and XMATCH either alone or in combination with FILTER and ISNUMER depending on the setup. I also have used FILTER with CHOOSECOLS. It seems that the more complex the lookuo situation the more I seem to rely on FILTER.
Thank you Richard for the sharing your preference. The reason I excluded the FILTER is because none of the others stands the competition :-)
What I tried to do, is to show everyone, the possible options in a way that those who do not have access to Dynamic Arrays, can still do the task. Of course, the functions you use are excellent.