Effortlessly LOOKUP ALL values between two dates (return many match results)
ฝัง
- เผยแพร่เมื่อ 10 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover how to effectively filter records between two dates using Excel formulas in this engaging tutorial. Perfect for Excel users with Office 365, this video guides you step-by-step to create dynamic, user-friendly reports.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/records-bet...
🔑 Key Highlights:
- Dynamic Filtering: Learn to use Excel's filter feature dynamically, where changing dates automatically updates your report.
- Drop-Down Creation: Master creating 'from' and 'to' date drop-downs, with the 'to date' displaying only dates after the selected 'from date'.
- Data Preparation: Understand how to prepare a unique list of dates for your drop-downs using Excel's dynamic array functions like UNIQUE and SORT.
- Data Validation: Dive into crafting data validation drop-down lists with new dynamic array functions, ensuring a user-friendly interface.
- Advanced Filtering: Excel at using the FILTER function for sophisticated data analysis, including handling multiple criteria and understanding the logic behind Excel formulas.
- Dynamic Arrays Application: Witness the power of dynamic arrays in action as you modify your data set and watch your report update automatically.
Tutorial Summary:
This video shows a practical example of using Excel's new Dynamic array functions to create a report that shows all records that fall between two dates. You learn:
1. How to use Dynamic array (# hash referencing) in data validation
2. How to create an Excel dependent drop down list
3. Create a FROM and TO Date drop down list where the TO date occurs AFTER the from date
4. Use the new Dynamic array SORT and UNIQUE functions
5. Use the new Excel Dynamic array Filter function (new Excel lookup formula) to return multiple match results
6. How to do an AND logical test with the Filter formula to return all values between two dates.
This is a knowledge packed tutorial covering the different aspects of dynamic arrays and Excel's new calculation engine.
LINK to Advanced Filter Feature: • Advanced Filter Excel ...
LINK to dynamic array playlist: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/records-between-dates
Is the "#" only working on the Office Insider as well? Haven't had the chance to try it out yet, but I've never heard of it on the the Office 365...
@@PellegriniCon Yes - that's Excel's new calculation engine - dynamic arrays and # and spilled ranges will all come together.
Ladies and Gentlemen, To raise the slogan "Long live to the Queen of Excel" thanx sweet Leila.
Oh, that's so nice. Thank you!
To be honest, I didn't want to learn array functions on weekend. Your videos are straight to the point and easy to follow. Thank you excel queen !
Leila, I've done this with calculations on a sheet of mine with 21,000 lines of records but your method tells me I've got a lot to learn. I know it's a great video because I've replayed it a number of times and paused it to take notes. Love your work.
I'm glad you like the video Ron.
Leila, the videos you produce are simply excellent. Some of the best I have found on TH-cam (and I've searched high and low)
This is great, it's like extra frosting on a cake! As always your tutorials are awesome! Thanks
Can’t wait for that course on dynamic arrays to be available, thank you Leila!
It will be out soon. Thanks for your support Billy.
Wow, an incredible amount of knowledge shared in 9 minutes. This is powerful and impressive I am geeked to logon to work on a Sunday to improve some workbooks.
Can't wait for this course to be out. Signed up and patiently waiting ... 😊
It will be soon ... :)
Oh so nice🙏🏻Tank you Leila!👏👏 For all great videos. This really help me in my work.
I'm so grateful for your videos and your courses👌🏼
Many thanks for the kind words Kenneth. I'm glad you find the tutorials helpful.
Your channel is a godsend! So many things I didn't even know I wanted to learn LOL! Every video has something that appli directly to need
This is Brilliant Leila. Thank you so much... I have sports workbooks where I have the whole season's fixtures entered. I can now use this table to see who is playing in the next few days or whatever period I want to see.
This is awesome, I have been doing this kind of thing the hard way! This method and functions will save me a lot of time. Thanks!!!
Brilliant! Clear and detailed as usual. A great example of the new After Dynamic Arrays (ADA) period, starting in excel.
Thanks Roberto. Yes, a new era is about to start :)
Thanks Leila.. great showcase of the power of DAFs and your new course which I'm sure will deep dive into more tricks and techniques to get the most out of the new functionality. Looking forward to more. Thumbs up!
Thank you Wayne. It's amazing how much simpler things have become with dynamic arrays. I just hope Microsoft rolls it out soon...
@@LeilaGharani Agreed.. although there is nothing like the satisfaction of getting a wild array formula to produce the result you want. In terms of getting work done fast, DAFs will really improve productivity. Looking forward to your course :))
That UNIQUE function is so useful, keep up the great work!
It definitely is!
love the way you explain such complicated problems in simple way
I'm glad it's easy to follow.
Leila, you are the best ! I like the way you explain, always when i have issues on excel i check out solutions through your channel, great job !
I'm very happy to hear that. Glad you find the videos informative.
it's just awesome. Filter function is truly a life saver. Thank for this informative video Leila.
You're welcome 😊
Thank you Leila. This is exactly that I've been looking for to use at work.
That's great you can apply it directly at work.
Dynamic Arrays.. Super Awesome!! I like the side where you demonstrate Multiplication of TRUE & FALSE.. Great Video 👍🏻
Glad you like the video. Yes, this will change the Excel game completely.
Thank you so much for excellent videos you make. I keep playing your videos several times. Wish you all the best.⭐️⭐️⭐️⭐️⭐️🌹🙏🥇
Great Excel guides and videos, really excellent work
Thanks Leila! Can't wait to have dynamic arrays in my Excel.
I hope it will be rolled out soon.
This is magnificent! Thank you for the help.
Many thanks Leila for your usual useful videos
You're most welcome Mohamed. Many thanks for your support.
Awesome, very helpful video. Thanks Leila.
Super viddy, Leila. Functions UNIQUE, SORT and FILTER are so powerful that their creation represents a sea change in Excel. One drawback here is that there needs to be the helper-table for the dates because DataValidation won't handle the arrays...yet. [And thank you for pronouncing it ZED - I'm tired of hearing ZEE!]
True, helper table is needed but hopefully we could solve this with name manager soon :)
Mam after wathing your every video I am prepare notes and use it's when I need, for me your lectures is very valuable for me, I am waiting your next video many times, you are really great....
I'm happy the tutorials are helpful for you.
Such and add-on to what I have been using in my report! It’ll make my life easier.
It definitely will Oliver. It's really a game changer.
Fantastic use of the Dynamic Arrays!
Thanks Oz - we need to find Satya!
This is extremely helpful. You're the best!👌
Glad you like the video!
Amazing, so easy to follow your videos. A true Excel-Queen as Daniel wrote .. 🙂
*Your are soft spoken, I like your voice.*I subscribed you right now*
Glad to have you here. Welcome to the community!
Thank you Leila for a very informative video. The question I have is that can you reference specific elements of a spill array so that you can pair them up with data that you've keyed in next to the spill array and reference both in a subsequent Vlookup or index match function. Thank you
Seriously so good knowledge sharing... It's really helpful and useful in my daily work
I'm really happy to hear it's useful in your daily work. That's how it should be.
Thank Leila, love the video, helpful as always, Herzliche Grüsse
🤗 🌞
Dankeschön :)
As usual so great work...stay blessed!
Thanks for the kind words Solomon.
Leila, this is just an excellent video, I enjoy learning about this topic from you, thanks
Glad you enjoyed it!
you are a lifesaver ... love it !!! Thanks
This is totally new for me . amezing LG..
It will really change the game. Glad you like it.
I'm lost for words... On a further note, i subscribed for your online course :)
I'm glad to hear that George. The course will be out in June.
Brilliant explanation and demonstration of why/how to use a dynamic array in excel
Glad you like it Darren.
Thank you for this channel! You have helped me so much. I spend many enjoyable hours listening and watching you to as I practice on my own.
Still, I would like to know if you have any suggestions on how to obtain a future date with a nest if formula with variable start dates. For example, if I complete a job on and my expected pay will wing it's way to me either the 15th or the 30th of next month dependent on if I performed my task before or after the 15 of this month. For added measure, could I add another criteria?
Awesome Leila as usual, Thank you..:)
Glad you like it. Thanks for your ongoing support.
Thanks Leila. You are great
Glad you like it Robert.
Very well explained!
Awesome tutorial Leila ... thank you
You're very welcome.
Lelia another great tutorial so clear thanks a lot
You're very welcome Nissim. Glad you like the video.
I am searching exact thing for my problem, thanks for this video, i always like your video
Happy to help :)
Hello Leila,
how i can get Filter Function in my excel version? Currently im using Excel 2019 and i don't have "Filter Function".
Hi Leila; Wonderful video !! For your video lovers like me, you are more like a user's manual to Excel and trust me its coming very much handy :) Cheers !
I'm very happy to hear that Sachin :) Many thanks for your ongoing support.
You are so good, very impressive!
Thanks for your wonderful video
Thank you very much, Leila.
You're very welcome.
Hi Leila, your biggest fan here :)
Please also cover power BI 🙏🏼🙏🏼🙏🏼
dear Leila, I do not have a UNIQUE function available in my excel 365 nor in my company excel. any thought about how it can be replaced? btw I'm your great fun :)))
Leila, You are awesome!!!!
I'm glad to hear that Samuel :)
You’re so damn good!
Thank you Leila!.
You're very welcome Conrado.
Awesome Leila, Lovely
Glad you like it Majid.
😲😲😲...Wowwww amazing!!!! 👏👏👏
Glad you like it Lucy :)
Great video, Leila! Like you said at the end, these dynamic array functions are still only available for Office Insiders. Just wondering if you know when they will be released in the main version?
No, sorry Andrew. Microsoft did not communicate an ETA for it yet.
I am watching your video quite frequently and have prepared notes so that the example of accurate use of formula and functions may be useful for my daily usage. Many of the functions I have used in routine files which have drastically changed the way I worked before. You make us understand so easily that we can learn it well.
Can you provide the facility to download the example files you use in your videos because some of the functions are so complexed which are not easily understandable in one go i.e. offset or nested functions.
Glad you like the video. You can find the link to the workbook in the description of the video.
Excellent work...thanks
Glad you like it!
Hi Leila,
Thanks a lot for your videos they are really very helpful.however, I'm struggling to put a formula where I can put vlookup if a date falls between two dates. Can I do that? Or please could you help me with putting a formula for a query like this?
Your help will be very appreciated.😁
Thanks for video!
You're very welcome.
Very usefull video!! Thank you
Glad you like it.
Hi, I have a question.
I work in company where there was a problem of checking if some drawing's index number was on server.
We could generate list in excel of indexes from our 3d program, and then some macro was used to check if specific indexes were on a disc or not.
It also modified the list by adding "empty" next to index that wasn't found on disc
Do you have a video showing how to do something like this macro I described?
great video as usual Leila. One question though. If I am not able to use the FILTER function, what other way would you recommend?
You can use filter function in google sheets or online Excel if you don't have office 365 or you can use the advanced filter function in the excel.
If you would like to add more requirements/filters/constraints to your report, such that it only displays the values you want, say Peter Ramsy, you simply alter the cell with the filter function (G7) and under ''include'' you add an extra * (B5:B53 = "Peter Ramsy") - now you will only get the report for Peter Ramsy. Note that the range is for the Sales Agents column. This is helpful if you have more ''Peter Ramsy''s or same identical values that you only want those to be displayed.
This is so useful. Ty so much sis
You're very welcome. Glad it's useful.
Awesome. Thanks
شكرا جزيلا think you
madam you are teaching very very good and easily understanding thank you madam
It's my pleasure
Thanks Leila.
You're very welcome.
Superb!!!!
Hello queen, thank u so much for this video, like every time, always special and useful solution, but i wonder if there is another functions that can replace the ( UNIQUE/FILTER & SORT ) for all formulas in this video.
Then, how to retrieve specific data from that list, for example: I want to list a certain Sales Agent between two dates?
Thankyou.
Excellent video.
Glad you like it.
Thank you
Thanks a lot
Thanks for your wonderful video You’re so damn good! Thanks
Glad you like the video!
hi tanks for your bet exel toturials.very nice toturial
Glad you like the tutorial.
👍Nice, great video mam "excel queen"👑
Thanks a lot
Great :-)
I did the last step on this tutorial converting the data to a table, but when I add a new record to the table it doesn't show up in the formulas, what am I doing wrong? Thank you for your help and great video
Ur vedio super very useful all
Thanks Madam.
Anyway I have never seen the coach like you who has the full command over each formula, function and DAX.
Thumbs up! :)
Hi Leila, That was very helpful, thank you :)
A question! I followed all the instructions that you said and I have office 365, however, the table is not dynamic and once I add new rows it doesn't go to my filtered list. Any idea how I can fix it?
Seems you have not declared a table and might have only put the filters on the headings.
Thanks Leila
You're very welcome.
Te Amo
Thank you Leila for your videos. I must say that I have learned a lot.
I was recently working with stocks data and wanted to filter out specific date data of multiple years, let's say the last trading day of the year for the years in the range.
I usually click the filter, and then have to manually click each day . Is there any better way?
Looking forward.
🌷Very nice mam 🌷Thanks🌹Amazing Quotes🌹
I'm glad you like it :)
Which would be faster in terms of calculations, this new filter formula or pivot table ? Sorry, just need advise.
This is great! Thank you - one question, How would I extend this feature to filter another column in the dataset? Ex. if column D in your list was product category.
Plze if i want 2 add a criteria
If i want this dataset 2 filter by agent only
Best Excel guides. I have a large list of date ranges (with start and end dates) with some overlaps; I want to count total number of unique days without counting overlaps multiple times. I've been brainstorming this for some time using sumifs(), but every idea comes short. Any hints? Thank you!
Great video, but what if I wanted to have the resulting dynamic table with one column with COUNTIF function that would tell me how many times the agent appears in the selected date range? I tried to use the FILTER function for Range in COUNTIF and spill it, but I always get an error.