Effortlessly LOOKUP ALL values between two dates (return many match results)
Вставка
- Опубліковано 24 лип 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!
Leila, the videos you produce are simply excellent. Some of the best I have found on UA-cam (and I've searched high and low)
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.
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.
This is great, it's like extra frosting on a cake! As always your tutorials are awesome! Thanks
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!!!
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.
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.
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
it's just awesome. Filter function is truly a life saver. Thank for this informative video Leila.
You're welcome 😊
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.
Thank you so much for excellent videos you make. I keep playing your videos several times. Wish you all the best.⭐️⭐️⭐️⭐️⭐️🌹🙏🥇
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 :)
love the way you explain such complicated problems in simple way
I'm glad it's easy to follow.
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.
You are so good, very impressive!
Can't wait for this course to be out. Signed up and patiently waiting ... 😊
It will be soon ... :)
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 :))
Great Excel guides and videos, really excellent work
you are a lifesaver ... love it !!! Thanks
Many thanks Leila for your usual useful videos
You're most welcome Mohamed. Many thanks for your support.
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
This is magnificent! Thank you for the help.
Awesome, very helpful video. Thanks Leila.
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.
That UNIQUE function is so useful, keep up the great work!
It definitely is!
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.
Amazing, so easy to follow your videos. A true Excel-Queen as Daniel wrote .. 🙂
This is extremely helpful. You're the best!👌
Glad you like the video!
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?
Very well explained!
Leila, this is just an excellent video, I enjoy learning about this topic from you, thanks
Glad you enjoyed it!
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.
Thanks Leila! Can't wait to have dynamic arrays in my Excel.
I hope it will be rolled out soon.
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.
Hello Leila,
how i can get Filter Function in my excel version? Currently im using Excel 2019 and i don't have "Filter Function".
As usual so great work...stay blessed!
Thanks for the kind words Solomon.
Thanks for your wonderful video
Brilliant explanation and demonstration of why/how to use a dynamic array in excel
Glad you like it Darren.
Awesome tutorial Leila ... thank you
You're very welcome.
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.
*Your are soft spoken, I like your voice.*I subscribed you right now*
Glad to have you here. Welcome to the community!
Thanks Leila. You are great
Glad you like it Robert.
Fantastic use of the Dynamic Arrays!
Thanks Oz - we need to find Satya!
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.😁
Awesome. Thanks
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?
Lelia another great tutorial so clear thanks a lot
You're very welcome Nissim. Glad you like the video.
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.
Thanks for video!
You're very welcome.
This is so useful. Ty so much sis
You're very welcome. Glad it's useful.
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 :)))
Thank Leila, love the video, helpful as always, Herzliche Grüsse
🤗 🌞
Dankeschön :)
hi tanks for your bet exel toturials.very nice toturial
Glad you like the tutorial.
Then, how to retrieve specific data from that list, for example: I want to list a certain Sales Agent between two dates?
Thankyou.
Awesome Leila as usual, Thank you..:)
Glad you like it. Thanks for your ongoing support.
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
Superb!!!!
Excellent video.
Glad you like it.
Thanks a lot
I am searching exact thing for my problem, thanks for this video, i always like your video
Happy to help :)
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.
Hi Leila, your biggest fan here :)
Please also cover power BI 🙏🏼🙏🏼🙏🏼
Ur vedio super very useful all
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.
Awesome Leila, Lovely
Glad you like it Majid.
Very usefull video!! Thank you
Glad you like it.
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.
This is totally new for me . amezing LG..
It will really change the game. Glad you like it.
Excellent work...thanks
Glad you like it!
Thank you
Hi! really helpful video however isit possible to change the values to a graph form?
thanks to this great tutorial. how about if data to be extracted are from different tabs. thank you
Thank you very much, Leila.
You're very welcome.
Thank you Leila , question is there any way that can find 3rd date between start and finish date with formula ?
شكرا جزيلا think you
Leila, You are awesome!!!!
I'm glad to hear that Samuel :)
😲😲😲...Wowwww amazing!!!! 👏👏👏
Glad you like it Lucy :)
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.
Thank you Leila!.
You're very welcome Conrado.
Very good
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 :)
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!
Question: is there a possibility, to see within the time filter, the total amount of sales per agent? Do you have a video for that?
Thanks Madam.
👍Nice, great video mam "excel queen"👑
Thanks a lot
madam you are teaching very very good and easily understanding thank you madam
It's my pleasure
Do you have a solution for this for other versions of excel where the filter option is not available?
excellent
Which would be faster in terms of calculations, this new filter formula or pivot table ? Sorry, just need advise.
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.
Is there a way to get the date a specific cell was modified?
somewhat similar to =GetLastModified() or adding a formula on a Table where every time I update a Field Value it updates another field with the timestamp or record creation
Hi Leila, thank you for all your videos, they help a lot. I wonder is you be kind to share a formula that I cannot make to work yet, I need to add total value of one code between dates, If I do it separayly just looking between dates it give the how many Code do I have, but no count the quantity of the code, the columns are DATE, CODE and QUANTITY. Can you help me with that, please?? thanks👍
You’re so damn good!
Hi Leila,
Please give a video class that how can I look up data from different sheets and workbook if possible so I need it so much.
Hi Leila thanks for all the good videos recently I have watched over 5 videos and all good and informative. The challange i have after watching this video is when i go to prep from and prep to and poulate the dates from my date column only 33 cells get copied and i have data upto 44 cells also the dates dont come over correctly - What am i doing wrong
Thanks Leila
You're very welcome.
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.