I just add a new practice problem to the download file. There were originally 10 practice problems that were all worksheet formula problems. But I added #11, which is a Power Query and DAX Approximate math practice problem. Now, the practice should be even more fun : ) : ) Check out the download.
What methods do people use for Approximate Match Lookup in Power Query? The custom function is painfully slow. But the two main methods that I know are Append (shown in video) and Merge (not shown in video). I timed the two methods on 350,000 rows of data and it seemed that the append method was faster. I have posted the workbook with my test results below video. What do others think? What methods do you use?
This solution is probably already been submitted. For HW8, I created the following formula FILTER(B13:E129,ISNUMBER(XMATCH(B13:B129,SEQUENCE(C8-C7+1,,C7)))) As I said, it has probably already been submitted.
I use Excel 40 hrs a week at work, essentially it is all I do , day in, day out, and I love it, (here I am on a Sat morning) but as the years go by, it puts a strain on your posture, neck, back etc, so I have become very conscious of the number of clicks it takes me to get to the solution, and utmost efficiency is my ever moving goal. Now, I may be biased because my No.1 Excel feature is definitely Formulas! :) but I find this YT channel is fast becoming my absolute favourite. I subscribe to many others channels which I also love, in particular VBA ones (my No. 2!) but even when I write applications I find that most of them incorporate WS formulas and I only find a way to write the code after I have found the way with formulas first, so definitely biased😊, thank you for all the work to put into your videos and books, it has already helped me write more efficient formulas and it will keep saving me so many clicks, too many to count!
Hello, great videos. Just wanted to point out at xlookup when doing two lookup values, you can do it in a bolean form: =Xlookup(1, (array=condition)*(array=condition)+(array=condition), return array) and it will read every * as an And operator and the + as an OR operator. Cheers
Great thorough video on lookup functions. I have always been a fan of LOOKUP and I have coomented many times that it is underused and indertaught. This video filled the "under taught" void. In fact I think you should clip the section of this video on LOOKUP and release It as an EMT. Many users could benefit from this as a stand alone video who may not find it imbeded in an hour plus long video.
Totally agree. I have many stand along LOOKUP videos and class videos with LOOKUP - because I am like you: trying to fill the undertaught void. First video I posted on LOOKUP was 14 years back. Obviously my years of LOOKUP diligence has done no good because still, no one knows about it lol
@@excelisfun i have seen several of those videos but I'm sure not all. I think this is your most through LOOKUP video and certainly the most comprehensive in the 365 context. That's why I suggested clipping it as an EMT. The SWITCH segment is especially significant. Not only is it a good use of SWITCH but it also illustrates how modern functiins can be used to expand the application domain of LOOKUP.
@@richardhay645 Nicely said: "good use of SWITCH but it also illustrates how modern functions can be used to expand the application domain of LOOKUP"!!!!! Thanks for the kudos for the LOOKUP section, Richard : ) : ) : ) : )
@@excelisfun bzTW your opening/clver image (not sure what UA-cam calls them) lists the majot lookup funcgtions covered in the video but LOOKUP is omited !?? It appears that there would be enough space wiith minimal rearrangement!!
thanks a lot, I was following you years ago and screwing my wits out with all those videos with superlong formulas with index match column vlookup, now the new 365 formulas made it much easier indeed, yet you made my wheels spin anyways creating new challenging situations. will have to watch the last part with data model more times, that was bit daunty but ..... TNX
Glad it helps. I am curious which methods you have seen? Which methods do you use? The two main methods I know for Approximate Match Lookup (that are not custom functions) are: 1) Append method (this video) and the 2) Merge method (more steps and I have not made a video on this). I have never seen a good analysis of which is faster. However, I did do a test on 350,000 rows of data and posted the test results below the video. My timing results showed that Append was about an average of 9 seconds to refresh and the merge method was about an average of 12 seconds to refresh.
The method I have seen is the merge method you mention. These are more steps And as you say is slower than the append method. Which is strange because usually merging Tables is a faster look up method.
@@cjimmer4877 I think merge is good for exact match. I think it would be ok for aprox match, but as you say, there are more steps. Where did you learn the merge method? When you use PQ merge for aprox match is it in Excel worksheet, Data Model, or Power BI?
@@excelisfun Learned from Mynda Treacy and several others. Her husband also uses the list functions for approximate match. This uses the let statement And requires a very good knowledge of M code. I have found using list functions for look up do not work so well on large datasets too slow. This is all done in the power query editor for Excel ws.
@@cjimmer4877 Yes, I have found that functions that pull a query into each row almost always make things too slow. I showed the function in this video, but only to remind us that it is slow lol It is funny, I searched the internet for the append method and could not find any mentions, all I could find in merge method. I learned the append method years ago from Bill Szysz.
You are an amazing Prof. I had to learn the basic excel from others and come again to understand the higher levels of Excel from you. Have become a fan of yours. Keep it up.
Even for the basics, Mike Playlist for Excel basics is perfect! Kinda more than just basics 😂👍 Some knowledge in this list would be labeled "advanced excel" in other channels. But it's basics in Mike's level 😂😂😂
Hi! So for 8:56 as long as the lookup array has the same number of columns as the return array, Xlookup will work right? So Xlookup needs the lookup array to either have the same number of columns OR rows as the return array in order to work right?
We must use Mixed Cell References in a Logical Formula in the Conditional Formatting dialog box. Here is a complete video of how: ua-cam.com/video/XCR3ReuRnTk/v-deo.html
This is pretty nice video to understand real use of age old LOOKUP function. I had always wondered about the real use case for this function. Thanks Mike!
Glad you liked the LOOKUP, Amardeep!! I have been using LOOKUP rather than VLOOKUP for my complex tax and commission formulas for about 15 years. It is just so much easier. Now with new XLOOKUP, LOOKUP still beats it : ) : )
God bless you. You are a tool for many people’s career. I strongly believe the passion and commitment is what it matters to be successful (more than the knowledge) You are 120/100
Hi Mike, great video guy! you are the best! On sheet "Random", for Random Draw Column I tried to perform this: =INDEX(B6:B15; UNIQUE(RANDARRAY(RANDBETWEEN(1; ROWS(B6:B15));;1; ROWS(B6:B15);TRUE);FALSE;FALSE)). Is working partially, but I can´t figure out how address the #SPILL! message, even with no records bellow sometimes appear 🤔. Why this?
At 28:21, wouldn't the XLOOKUP() function with the MAXIFS() function as the criteria1 be better than SUMIFS(), since, if the sales reps had more than one sale on that date, SUMIFS() would add them all together?
@@excelisfun I meant last to first. :) of course the best way is to make sure we have some type of transaction numbers to make an accidental sort not such a problem.
It sounds like your computer has Excel 2010, not M 365. Sounds like you need to buy the new version. But really, I have no idea what is happening on your computer.
hh:mm:ss 00: 31:02 averageifs function is not taking zero of starting sir. It's getting #DIV/0! I don't want to remove zero. How to write formula forit
@@excelisfun Hi. I Billing Person. I Have doubt of my. In A column I have write Dates sir. And B Cloumn I have written time of each bill I have taken. In separate cell I write formula of Averageif function to calculate average time I have taken for each bill on that day. I am getting error of #DIV/0!.
I like that you provide PDF notes for every video. Do you have a pretty simple method for creating these? I imagine they take awhile to complete depending on the content. Thanks
Absolutely 100 % no. They are usually not easy to create. Although sometimes pdf notes like in this video do not take much time because they are screen shots of the Excel sheets, other times like in the next video MECS #9, or video #9 in my MSPTDA class, or in video #16 in my Statistics class, the pdf notes take longer than the videos. Here are the two scenarios: if in a video like this one, MECS video #8, I write the script in the Excel worksheet, then most of the time is spent in Excel and not the pdf. But other videos like the ones mentioned, I write the script as if it will be read in the pdf notes as a reading document, these take a very long time. As for table of contents, that is just a Word trick using the Heading 1 and Heading 2 styles. Also, converting a Word to PDF is as easy as the F12 key : )
@@excelisfun Makes sense. A different topic but does anyone ever ask you for a complete list of all your video content? I ask because I have a site in conjunction with my channel. I've always felt compelled to make a full video archive on my site. As we both know, UA-cam's main channel page settings allowing us so showcase playlists and such is still a bit limited, I try my best with it though. I just the idea of ALL of my content easily available for those who need it.
@@PlaybookGamer Yes, people ask for a full list. I do not have one. AND YES!!!!: UA-cam is so limited with it's home page display. I can't believe that they only let us have 10 playlist. I would have at least 50 if I could. Not only that, but it is deeply offensive and egregious that UA-cam does not have the ability for viewers to search for playlists. Have you ever tried to use the channel search to search for a playlist? I have NEVER gotten it to work. In the early days of UA-cam, I worked with UA-cam managers, and they were super nice, but UA-cam has made many bad decisions that really hurt us UA-cam creators. Simple stuff that seems so obvious, but it just doesn't work in the UA-cam Channel user interface... : (
@@excelisfun UA-cam now lets us create multiples playlists sections which is my workaround for showing a bunch of stuff at once. Still, I feel like a full-fledge video archive is needed on my end and throwing all of that on the site seems needed.
@@excelisfun I am watching now the end of the video, One other Thing Mike, in the approximate Match with power Query we can solve This with Column From Example which is converted automatically in conditional Column and adjust it if we need by replacing each limit :-)
@@vijayraghavanr1 I am not very good with VBA. And I only will have one Macro video. I have one already that covers the material: ua-cam.com/video/WKyN8e7XXjI/v-deo.html
I see, I have missed a lot of great stuff, Will try to cover all your videos in the next couple of weeks. My illness kept me out of action for more than 2 months. Good to be back on this channel. Hope life is keeping you good and healthy Mike
Hello Mike, Thanks for creating such an amazing content on Excel on UA-cam. I have been following you on UA-cam and have found your videos to be immensely helpful. I can already see the impact they make in my professional life especially when I get appreciation of my colleagues for all the magic tricks that I have learned from your videos. I cannot be more thankful to you for this. I have a question about example no. 3 where you demonstrate how to look up a column of values and sum them up. I was trying to create a single cell formula to create a dynamic report with totals on the last column and last row whose location would change dynamically based on the number of rows and columns. I used the knowledge I had gained from your video series on dynamic arrays. I had a similar data structure as in your example no. 3. In creating a formula which would calculate and display total on the last row/last column, I tried to pass the dynamic column/rows field names as look up value to Xlookup but it shows me value error. I also tried using index match/xmatch but it didn't work either. However, when I try to pass a single column/row field name as a look up value to Xlookup, it works but then it would require me to copy the formula to the corresponding columns (for last row totals) and that is what I want to avoid. What would you suggest that I can do in this situation? I would really appreciate your help in this.
I do not totally understand what you are asking. But I am slow to understand... I have videos about dynamic total rows. Here are three that use the latest VSATCK and HSATCK: ua-cam.com/video/17U8_6besyI/v-deo.html ua-cam.com/video/dHySYFz4Dzc/v-deo.html ua-cam.com/video/L0KY7pHgudM/v-deo.html Here is a video before we had VSATCK and HSATCK (MUCH harder): ua-cam.com/video/SGC8WyUz0bE/v-deo.html
@@excelisfun Thank you so much for your response and providing with links to amazing videos. Vstack and Hstack are unfortunately not included in the office 365 version that is installed on my company's laptop. I have finally been able to accomplish passing of dynamic lookup values to Xlookup by using address, indirect and sequence functions, IF and IFERROR. I used EMT 1528 as a reference. But the final single cell formula had to be entered using cntrl+shift+enter and while doing calculations, it would take more than a couple of seconds or so to update. I feel happy that I could accomplish it but if it had been a little faster in execution, I would have been more happy.
I just add a new practice problem to the download file. There were originally 10 practice problems that were all worksheet formula problems. But I added #11, which is a Power Query and DAX Approximate math practice problem. Now, the practice should be even more fun : ) : ) Check out the download.
Thank you very much for the free courses, PLEASE can you also start quickbooks or tally
What methods do people use for Approximate Match Lookup in Power Query? The custom function is painfully slow. But the two main methods that I know are Append (shown in video) and Merge (not shown in video). I timed the two methods on 350,000 rows of data and it seemed that the append method was faster. I have posted the workbook with my test results below video. What do others think? What methods do you use?
This solution is probably already been submitted. For HW8, I created the following formula
FILTER(B13:E129,ISNUMBER(XMATCH(B13:B129,SEQUENCE(C8-C7+1,,C7))))
As I said, it has probably already been submitted.
I use Excel 40 hrs a week at work, essentially it is all I do , day in, day out, and I love it, (here I am on a Sat morning) but as the years go by, it puts a strain on your posture, neck, back etc, so I have become very conscious of the number of clicks it takes me to get to the solution, and utmost efficiency is my ever moving goal. Now, I may be biased because my No.1 Excel feature is definitely Formulas! :) but I find this YT channel is fast becoming my absolute favourite. I subscribe to many others channels which I also love, in particular VBA ones (my No. 2!) but even when I write applications I find that most of them incorporate WS formulas and I only find a way to write the code after I have found the way with formulas first, so definitely biased😊, thank you for all the work to put into your videos and books, it has already helped me write more efficient formulas and it will keep saving me so many clicks, too many to count!
I am so glad to help! I am exactly like you: 40-80 hours a week in Excel so every click matters : ) : ) Keep watching and having efficient fun!!
Hello, great videos.
Just wanted to point out at xlookup when doing two lookup values, you can do it in a bolean form: =Xlookup(1, (array=condition)*(array=condition)+(array=condition), return array) and it will read every * as an And operator and the + as an OR operator.
Cheers
Can you give an example?
Thanks, glad I found your channel. You're a great teacher
Glad to help with the fun : )
One of the best tutorial about xlookup.Thank you.
Glad it helps!!!!
Great thorough video on lookup functions. I have always been a fan of LOOKUP and I have coomented many times that it is underused and indertaught. This video filled the "under taught" void. In fact I think you should clip the section of this video on LOOKUP and release It as an EMT.
Many users could benefit from this as a stand alone video who may not find it imbeded in an hour plus long video.
Totally agree. I have many stand along LOOKUP videos and class videos with LOOKUP - because I am like you: trying to fill the undertaught void. First video I posted on LOOKUP was 14 years back. Obviously my years of LOOKUP diligence has done no good because still, no one knows about it lol
@@excelisfun i have seen several of those videos but I'm sure not all. I think this is your most through LOOKUP video and certainly the most comprehensive in the 365 context. That's why I suggested clipping it as an EMT. The SWITCH segment is especially significant. Not only is it a good use of SWITCH but it also illustrates how modern functiins can be used to expand the application domain of LOOKUP.
@@richardhay645 Nicely said: "good use of SWITCH but it also illustrates how modern functions can be used to expand the application domain of LOOKUP"!!!!! Thanks for the kudos for the LOOKUP section, Richard : ) : ) : ) : )
@@excelisfun bzTW your opening/clver image (not sure what UA-cam calls them) lists the majot lookup funcgtions covered in the video but LOOKUP is omited !?? It appears that there would be enough space wiith minimal rearrangement!!
@@richardhay645 Good point
Xlookup + range refence operator - just wow.
I agree: Way Fun : ) : )
thanks a lot, I was following you years ago and screwing my wits out with all those videos with superlong formulas with index match column vlookup, now the new 365 formulas made it much easier indeed, yet you made my wheels spin anyways creating new challenging situations. will have to watch the last part with data model more times, that was bit daunty but ..... TNX
You are welcome, Alberto!!!!
Hi Mike, can you explain how that condition format with lookup works (=LOOKUP($B$5;$B$10:$B$15)=$B10) i didi not get it. Please
I consume a lot of power query videos. This is the 1st time I've seen such a clever use of approximate match in power query using sort functions.
Glad it helps. I am curious which methods you have seen? Which methods do you use?
The two main methods I know for Approximate Match Lookup (that are not custom functions) are: 1) Append method (this video) and the 2) Merge method (more steps and I have not made a video on this). I have never seen a good analysis of which is faster. However, I did do a test on 350,000 rows of data and posted the test results below the video. My timing results showed that Append was about an average of 9 seconds to refresh and the merge method was about an average of 12 seconds to refresh.
The method I have seen is the merge method you mention. These are more steps And as you say is slower than the append method. Which is strange because usually merging Tables is a faster look up method.
@@cjimmer4877 I think merge is good for exact match. I think it would be ok for aprox match, but as you say, there are more steps. Where did you learn the merge method? When you use PQ merge for aprox match is it in Excel worksheet, Data Model, or Power BI?
@@excelisfun
Learned from Mynda Treacy and several others. Her husband also uses the list functions for approximate match. This uses the let statement And requires a very good knowledge of M code. I have found using list functions for look up do not work so well on large datasets too slow. This is all done in the power query editor for Excel ws.
@@cjimmer4877 Yes, I have found that functions that pull a query into each row almost always make things too slow. I showed the function in this video, but only to remind us that it is slow lol
It is funny, I searched the internet for the append method and could not find any mentions, all I could find in merge method. I learned the append method years ago from Bill Szysz.
You are an amazing Prof. I had to learn the basic excel from others and come again to understand the higher levels of Excel from you. Have become a fan of yours. Keep it up.
I am so glad to help, Zakeer!!!!!
same for me without Girvin I would not be at the level I am now, I basically built a career with this knowledge
@@excelisfun God bless you. I strongly believe a passion and commitment is what it matters to be successful (more than the knowledge)
You are 120/100
Even for the basics, Mike Playlist for Excel basics is perfect! Kinda more than just basics 😂👍
Some knowledge in this list would be labeled "advanced excel" in other channels. But it's basics in Mike's level 😂😂😂
@@Alberto-hr1cf That is what I do: help you and the rest of the Team be awesome with Excel!!! I am happy to help : )
Hi! So for 8:56 as long as the lookup array has the same number of columns as the return array, Xlookup will work right? So Xlookup needs the lookup array to either have the same number of columns OR rows as the return array in order to work right?
Boom!Truly Awesome Super Fun Class...Thank You Mike :)
You are TRUEly welcome, Bike Brother : ) : )
OMG! Thank you so much!!!❤❤❤
You are welcome so much, Ume!!!!
Thank you so much Amazing Mike for this EXCELlent video.
You are welcome, Most Awesome Fellow Teacher!!!!
Trying to download your Excel-file and the PDF, I got a warning sign that these downloads are not safe ...
That was a great Video Mike. Thanks :) :)
You are welcome, Formula Guy John : ) : )
Another great vid another learning
! Thanks
You are welcome, Dan!!!!!
Super cool 😎😎👍😸😸😸
Yes!!!! Vijay : ) : )
very informative
Glad this helps, Naushad!!!!
Great video ❤️👌
Glad you like it, shubham : ) : ) : )
Great video! How did you get the column to highlight yellow when you selected an option from the data validation cells?
We must use Mixed Cell References in a Logical Formula in the Conditional Formatting dialog box. Here is a complete video of how: ua-cam.com/video/XCR3ReuRnTk/v-deo.html
Here is one for Approximate Match Lookup: ua-cam.com/video/FuPdQvOFZkw/v-deo.html
I really Love the Approx-Match within PQ, it's just crazy logical. 🖖
Yes, me too, Roger!!!!
This is pretty nice video to understand real use of age old LOOKUP function. I had always wondered about the real use case for this function. Thanks Mike!
Glad you liked the LOOKUP, Amardeep!! I have been using LOOKUP rather than VLOOKUP for my complex tax and commission formulas for about 15 years. It is just so much easier. Now with new XLOOKUP, LOOKUP still beats it : ) : )
@@excelisfun Yeah, I can relate to this after watching your video 😀
@@amardeepsingh5252 : ) : ) : ) : )
You are always thorough, excellent topic! Watching now!
Enjoy the watching now, Jeranon!!!!!
Epic Video
: ) : )
Thankyou, Great Content got more clarity in Power Pivot and Power query with this video, all previous videos have been amazing and the exercises too!!
Glad you like it all!!!!
Truly a beneficial video. Thankyou very much for your efforts, Sir. 😊😄
You are welcome, Vikas!!!!
Thank you Mike, appreciated the PQ solutions. On a mission to master PQ before going over to DAX and Power BI.
If you know worksheet, M Code and DAX - man : ) : ) That is a lot of Power!! I am glad my videos help, Lester!!
This is amazing ... the xlookup part was briliant ... Thanks Mike
You are welcome for the XLOOKUP fin, Hussein!!!
God bless you. You are a tool for many people’s career.
I strongly believe the passion and commitment is what it matters to be successful (more than the knowledge)
You are 120/100
Yes!!!!!
Great video. Especially loved the PQ part at the end. Fabulous.
Glad you like it all, John!!!!!
Hi Mike, great video guy! you are the best! On sheet "Random", for Random Draw Column I tried to perform this: =INDEX(B6:B15; UNIQUE(RANDARRAY(RANDBETWEEN(1; ROWS(B6:B15));;1; ROWS(B6:B15);TRUE);FALSE;FALSE)). Is working partially, but I can´t figure out how address the #SPILL! message, even with no records bellow sometimes appear 🤔. Why this?
At 28:21, wouldn't the XLOOKUP() function with the MAXIFS() function as the criteria1 be better than SUMIFS(), since, if the sales reps had more than one sale on that date, SUMIFS() would add them all together?
I guess it depends. XLOOKUP would get first encounter of dup, SUMIFS would add them both.
@@excelisfun I meant last to first. :) of course the best way is to make sure we have some type of transaction numbers to make an accidental sort not such a problem.
thank you very much Professor.
God bless you.
You are welcome for the lookup fun, jamal!!!!!
Thank you very much Professor. Looking forward to watching this weekend. It looks like a TON of great information!!! 👍👍👍
It is THE one-stop Excel Lookup destination for sure. Have a great weekend with all the fun lookup formulas and methods, Kevin!!!
Indeed another great video
Glad you like this video, Rajat!!!!!
how do i open the download link in excel 365 it keeps trying to open in office 2010.
It sounds like your computer has Excel 2010, not M 365. Sounds like you need to buy the new version. But really, I have no idea what is happening on your computer.
hh:mm:ss
00: 31:02
averageifs function is not taking zero of starting sir. It's getting #DIV/0! I don't want to remove zero. How to write formula forit
I do not understand what you are asking. I see no AVERAGEIFS at 00:31:02
@@excelisfun Hi. I Billing Person. I Have doubt of my. In A column I have write Dates sir. And B Cloumn I have written time of each bill I have taken. In separate cell I write formula of Averageif function to calculate average time I have taken for each bill on that day. I am getting error of #DIV/0!.
Thanks Mike! This will be a good refresher
Lookup is fun, especially looking up tables and doing aprox match in PQ and DAX : ) You are welcome as always, Chris M!!!!
Thank you very much, Mike!
You are welcome very much, Luciano!!! : )
I like that you provide PDF notes for every video. Do you have a pretty simple method for creating these? I imagine they take awhile to complete depending on the content. Thanks
Absolutely 100 % no. They are usually not easy to create. Although sometimes pdf notes like in this video do not take much time because they are screen shots of the Excel sheets, other times like in the next video MECS #9, or video #9 in my MSPTDA class, or in video #16 in my Statistics class, the pdf notes take longer than the videos. Here are the two scenarios: if in a video like this one, MECS video #8, I write the script in the Excel worksheet, then most of the time is spent in Excel and not the pdf. But other videos like the ones mentioned, I write the script as if it will be read in the pdf notes as a reading document, these take a very long time. As for table of contents, that is just a Word trick using the Heading 1 and Heading 2 styles. Also, converting a Word to PDF is as easy as the F12 key : )
@@excelisfun Makes sense. A different topic but does anyone ever ask you for a complete list of all your video content? I ask because I have a site in conjunction with my channel. I've always felt compelled to make a full video archive on my site. As we both know, UA-cam's main channel page settings allowing us so showcase playlists and such is still a bit limited, I try my best with it though. I just the idea of ALL of my content easily available for those who need it.
@@PlaybookGamer Yes, people ask for a full list. I do not have one. AND YES!!!!: UA-cam is so limited with it's home page display. I can't believe that they only let us have 10 playlist. I would have at least 50 if I could. Not only that, but it is deeply offensive and egregious that UA-cam does not have the ability for viewers to search for playlists. Have you ever tried to use the channel search to search for a playlist? I have NEVER gotten it to work. In the early days of UA-cam, I worked with UA-cam managers, and they were super nice, but UA-cam has made many bad decisions that really hurt us UA-cam creators. Simple stuff that seems so obvious, but it just doesn't work in the UA-cam Channel user interface... : (
@@excelisfun UA-cam now lets us create multiples playlists sections which is my workaround for showing a bunch of stuff at once. Still, I feel like a full-fledge video archive is needed on my end and throwing all of that on the site seems needed.
@@PlaybookGamer But on the home page I can only put up to 10 playlists. Do you know how to add more?
Sure we like this video. Amazing trick in 36:30 and 47:00. A lot of fun teacher mike : -)
Cool, Mohamed!!! Yes, the lookup range trick and random column are great tricks : )
@@excelisfun I am watching now the end of the video, One other Thing Mike, in the approximate Match with power Query we can solve This with Column From Example which is converted automatically in conditional Column and adjust it if we need by replacing each limit :-)
@@mohamedchakroun4973 I never thought of doing it that way.... : 0
@@excelisfun yes excel is very very amazing we can solve one problem with many ways :-)
First to view like and comment 👍
Yes!!!! You get the first place trophy, Vijay : ) : ) : ) : )
Eagerly awaiting videos on VBA Macros 👍
@@vijayraghavanr1 I am not very good with VBA. And I only will have one Macro video. I have one already that covers the material:
ua-cam.com/video/WKyN8e7XXjI/v-deo.html
@@excelisfun Thank you 👍
@@vijayraghavanr1 You are welcome : )
Great as usual Mike, thank you. I have a question: "How to select a unique random employee names from a list?"
use UNIQUE(), then COUNT() and use INDEX() with RANDBETWEEN() as the row number. =INDEX(UNIQUE(name_range),RANDBETWEEN(1,COUNT(UNIQUE(name_range))))
Thanks
Thank you soooooooooo much, Teammate Santosh : ) : ) : )
I see, I have missed a lot of great stuff, Will try to cover all your videos in the next couple of weeks. My illness kept me out of action for more than 2 months. Good to be back on this channel. Hope life is keeping you good and healthy Mike
@@msantosh1220 I am sorry to hear about bad health. I hope you will get better soon and that my videos can help you have some fun : ) : ) : ) : )
Yes, they well .
Happy Diwali Mike,
May the light and warmth of lamp (Diya) be with you🌟🎇
@@msantosh1220 : ) : ) : ) Boomerangi9ng back at you too : )
Hello Mike, Thanks for creating such an amazing content on Excel on UA-cam. I have been following you on UA-cam and have found your videos to be immensely helpful. I can already see the impact they make in my professional life especially when I get appreciation of my colleagues for all the magic tricks that I have learned from your videos. I cannot be more thankful to you for this.
I have a question about example no. 3 where you demonstrate how to look up a column of values and sum them up. I was trying to create a single cell formula to create a dynamic report with totals on the last column and last row whose location would change dynamically based on the number of rows and columns. I used the knowledge I had gained from your video series on dynamic arrays. I had a similar data structure as in your example no. 3. In creating a formula which would calculate and display total on the last row/last column, I tried to pass the dynamic column/rows field names as look up value to Xlookup but it shows me value error. I also tried using index match/xmatch but it didn't work either. However, when I try to pass a single column/row field name as a look up value to Xlookup, it works but then it would require me to copy the formula to the corresponding columns (for last row totals) and that is what I want to avoid. What would you suggest that I can do in this situation? I would really appreciate your help in this.
I do not totally understand what you are asking. But I am slow to understand... I have videos about dynamic total rows.
Here are three that use the latest VSATCK and HSATCK:
ua-cam.com/video/17U8_6besyI/v-deo.html
ua-cam.com/video/dHySYFz4Dzc/v-deo.html
ua-cam.com/video/L0KY7pHgudM/v-deo.html
Here is a video before we had VSATCK and HSATCK (MUCH harder):
ua-cam.com/video/SGC8WyUz0bE/v-deo.html
I am glad that I have been able to help you professionally. Thanks for your support : )
@@excelisfun Thank you so much for your response and providing with links to amazing videos. Vstack and Hstack are unfortunately not included in the office 365 version that is installed on my company's laptop.
I have finally been able to accomplish passing of dynamic lookup values to Xlookup by using address, indirect and sequence functions, IF and IFERROR. I used EMT 1528 as a reference. But the final single cell formula had to be entered using cntrl+shift+enter and while doing calculations, it would take more than a couple of seconds or so to update.
I feel happy that I could accomplish it but if it had been a little faster in execution, I would have been more happy.
Kama, Kama, Kamiliijaaaa