All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX - 28 Examples! - 365 MECS 08

[@excelisfun]

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.

[@fareedboy7872]

Thank you very much for the free courses, PLEASE can you also start quickbooks or tally

[@lourdesdelcampo8722]

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!

[@excelisfun]

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!!

[@LucianLutaru]

One of the best tutorial about xlookup.Thank you.

[@excelisfun]

Glad it helps!!!!

[@excelisfun]

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?

[@MrSpartelius]

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

[@aleksandarsirakov3648]

Can you give an example?

[@cjimmer4877]

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.

[@excelisfun]

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.

[@cjimmer4877]

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.

[@excelisfun]

@@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?

[@cjimmer4877]

@@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.

[@excelisfun]

@@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.

[@hdawg2697]

Thanks, glad I found your channel. You're a great teacher

[@excelisfun]

Glad to help with the fun : )

[@richardhay645]

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.

[@excelisfun]

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

[@richardhay645]

@@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.

[@excelisfun]

@@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 : ) : ) : ) : )

[@richardhay645]

@@excelisfun bzTW your opening/clver image (not sure what KZbin 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!!

[@excelisfun]

@@richardhay645 Good point

[@Alberto-hr1cf]

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

[@excelisfun]

You are welcome, Alberto!!!!

[@zakeerrg3010]

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.

[@excelisfun]

I am so glad to help, Zakeer!!!!!

[@Alberto-hr1cf]

same for me without Girvin I would not be at the level I am now, I basically built a career with this knowledge

[@zakeerrg3010]

@@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

[@Ali.Mostafa]

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 😂😂😂

[@excelisfun]

@@Alberto-hr1cf That is what I do: help you and the rest of the Team be awesome with Excel!!! I am happy to help : )

[@jc7671]

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.

[@amardeepsingh5252]

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!

[@excelisfun]

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 : ) : )

[@amardeepsingh5252]

@@excelisfun Yeah, I can relate to this after watching your video 😀

[@excelisfun]

@@amardeepsingh5252 : ) : ) : ) : )

[@zakeerrg3010]

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

[@excelisfun]

Yes!!!!!

[@omkar6190]

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!!

[@excelisfun]

Glad you like it all!!!!

[@jeranon43]

You are always thorough, excellent topic! Watching now!

[@excelisfun]

Enjoy the watching now, Jeranon!!!!!

[@RogerStocker]

I really Love the Approx-Match within PQ, it's just crazy logical. 🖖

[@excelisfun]

Yes, me too, Roger!!!!

[@johnadair4979]

Great video. Especially loved the PQ part at the end. Fabulous.

[@excelisfun]

Glad you like it all, John!!!!!

[@excel_along_the_way]

Thank you Mike, appreciated the PQ solutions. On a mission to master PQ before going over to DAX and Power BI.

[@excelisfun]

If you know worksheet, M Code and DAX - man : ) : ) That is a lot of Power!! I am glad my videos help, Lester!!

[@DanAlvard]

Another great vid another learning ! Thanks

[@excelisfun]

You are welcome, Dan!!!!!

[@HusseinKorish]

This is amazing ... the xlookup part was briliant ... Thanks Mike

[@excelisfun]

You are welcome for the XLOOKUP fin, Hussein!!!

[@patrickc1862]

Great video! How did you get the column to highlight yellow when you selected an option from the data validation cells?

[@excelisfun]

We must use Mixed Cell References in a Logical Formula in the Conditional Formatting dialog box. Here is a complete video of how: kzbin.info/www/bejne/jnS1ZIWbqrehis0

[@excelisfun]

Here is one for Approximate Match Lookup: kzbin.info/www/bejne/fKazlYSshKuNodk

[@jamalahmed5327]

thank you very much Professor. God bless you.

[@excelisfun]

You are welcome for the lookup fun, jamal!!!!!

[@chrism9037]

Thanks Mike! This will be a good refresher

[@excelisfun]

Lookup is fun, especially looking up tables and doing aprox match in PQ and DAX : ) You are welcome as always, Chris M!!!!

[@Name_VS]

Truly a beneficial video. Thankyou very much for your efforts, Sir. 😊😄

[@excelisfun]

You are welcome, Vikas!!!!

[@darrylmorgan]

Boom!Truly Awesome Super Fun Class...Thank You Mike :)

[@excelisfun]

You are TRUEly welcome, Bike Brother : ) : )

[@KevinPGA]

Thank you very much Professor. Looking forward to watching this weekend. It looks like a TON of great information!!! 👍👍👍

[@excelisfun]

It is THE one-stop Excel Lookup destination for sure. Have a great weekend with all the fun lookup formulas and methods, Kevin!!!

[@rajatgarg1341]

Indeed another great video

[@excelisfun]

Glad you like this video, Rajat!!!!!

[@AtanasNenov]

Xlookup + range refence operator - just wow.

[@excelisfun]

I agree: Way Fun : ) : )

[@dantobuscus8310]

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?

[@SyedMuzammilMahasanShahi]

Thank you so much Amazing Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Most Awesome Fellow Teacher!!!!

[@Luciano_mp]

Thank you very much, Mike!

[@excelisfun]

You are welcome very much, Luciano!!! : )

[@johnborg5419]

That was a great Video Mike. Thanks :) :)

[@excelisfun]

You are welcome, Formula Guy John : ) : )

[@umepervaiz1488]

OMG! Thank you so much!!!❤❤❤

[@excelisfun]

You are welcome so much, Ume!!!!

[@naushad123]

very informative

[@excelisfun]

Glad this helps, Naushad!!!!

[@PlaybookGamer]

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

[@excelisfun]

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 : )

[@PlaybookGamer]

@@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, KZbin'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.

[@excelisfun]

@@PlaybookGamer Yes, people ask for a full list. I do not have one. AND YES!!!!: KZbin 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 KZbin 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 KZbin, I worked with KZbin managers, and they were super nice, but KZbin has made many bad decisions that really hurt us KZbin creators. Simple stuff that seems so obvious, but it just doesn't work in the KZbin Channel user interface... : (

[@PlaybookGamer]

@@excelisfun KZbin 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]

@@PlaybookGamer But on the home page I can only put up to 10 playlists. Do you know how to add more?

[@shubhampawar8506]

Great video ❤️👌

[@excelisfun]

Glad you like it, shubham : ) : ) : )

[@lucaviglio1206]

Epic Video

[@excelisfun]

: ) : )

[@alexfurtado1759]

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

[@suraexcel8370]

Great as usual Mike, thank you. I have a question: "How to select a unique random employee names from a list?"

[@MaydayAggro]

use UNIQUE(), then COUNT() and use INDEX() with RANDBETWEEN() as the row number. =INDEX(UNIQUE(name_range),RANDBETWEEN(1,COUNT(UNIQUE(name_range))))

[@saurabhbhardwaj9667]

Hello Mike, Thanks for creating such an amazing content on Excel on KZbin. I have been following you on KZbin 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.

[@excelisfun]

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: kzbin.info/www/bejne/Z2i4aZJsl8qmr6s kzbin.info/www/bejne/mnnchIx8r5l3sMU kzbin.info/www/bejne/gmGuimqmfcyomq8 Here is a video before we had VSATCK and HSATCK (MUCH harder): kzbin.info/www/bejne/iXimaYqvit9jmKc

[@excelisfun]

I am glad that I have been able to help you professionally. Thanks for your support : )

[@saurabhbhardwaj9667]

@@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.

[@gvitullib]

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?

[@mohamedchakroun4973]

Sure we like this video. Amazing trick in 36:30 and 47:00. A lot of fun teacher mike : -)

[@excelisfun]

Cool, Mohamed!!! Yes, the lookup range trick and random column are great tricks : )

[@mohamedchakroun4973]

@@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 :-)

[@excelisfun]

@@mohamedchakroun4973 I never thought of doing it that way.... : 0

[@mohamedchakroun4973]

@@excelisfun yes excel is very very amazing we can solve one problem with many ways :-)

[@vijaysahal4556]

Super cool 😎😎👍😸😸😸

[@excelisfun]

Yes!!!! Vijay : ) : )

[@maynard04]

how do i open the download link in excel 365 it keeps trying to open in office 2010.

[@excelisfun]

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.

[@msantosh1220]

Thanks

[@excelisfun]

Thank you soooooooooo much, Teammate Santosh : ) : ) : )

[@msantosh1220]

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

[@excelisfun]

@@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 : ) : ) : ) : )

[@msantosh1220]

Yes, they well . Happy Diwali Mike, May the light and warmth of lamp (Diya) be with you🌟🎇

[@excelisfun]

@@msantosh1220 : ) : ) : ) Boomerangi9ng back at you too : )

[@MaydayAggro]

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 guess it depends. XLOOKUP would get first encounter of dup, SUMIFS would add them both.

[@MaydayAggro]

@@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.

[@sraja2079]

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]

I do not understand what you are asking. I see no AVERAGEIFS at 00:31:02

[@sraja2079]

@@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!.

[@vijayraghavanr1]

First to view like and comment 👍

[@excelisfun]

Yes!!!! You get the first place trophy, Vijay : ) : ) : ) : )

[@vijayraghavanr1]

Eagerly awaiting videos on VBA Macros 👍

[@excelisfun]

@@vijayraghavanr1 I am not very good with VBA. And I only will have one Macro video. I have one already that covers the material: kzbin.info/www/bejne/jXzcf2ubbL2LoKs

[@vijayraghavanr1]

@@excelisfun Thank you 👍

[@excelisfun]

@@vijayraghavanr1 You are welcome : )

[@bertvantol9669]

Trying to download your Excel-file and the PDF, I got a warning sign that these downloads are not safe ...

[@obi4830]

Kama, Kama, Kamiliijaaaa