Excel Formula: Lookup Next Date of a Value after Today
ฝัง
- เผยแพร่เมื่อ 12 ก.ค. 2018
- This video will use an Excel formula to look up the date of the first occurrence of a value after today's date.
In the example, we have a list of courses running each day. We want to easily find the next course to run after today.
This is accomplished by using the wonderful INDEX and MATCH functions together in an array formula.
In the video, we walk through the scenario and the solution. We then explain how it works in detail.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - แนวปฏิบัติและการใช้ชีวิต
This is brilliant. Just what I am after. Thank you so much for sharing in carefully detailed way.
My pleasure. I'm glad it helped.
it's the best excel tutorial i've ever watched! great job!
Thank you very much.
Thanks Alan. Welk explained. I used to use a helper column and use &. This is far better...
Thanks Bart. Yes, I have done that too.
Dear, thank you so much for this video. You are a life saver and you explain it very understandable. You are out there helping people! Cheers! :)
You're very welcome! Thank you for your comments, Belosveta.
Thank you so much for this man!
You're very welcome Cesar.
absolute genius
😊
What I don't like with this command is that it looks for the "next" date based on the order it is in the list, not the next calendar date. I like to know how to get the next calendar date, no matter where it is in the column or row the search is?
Question: what if you wanted to get a hit on the second “1” with all things staying constant? I’m thinking the formula in the ‘lookup value” of the “match” syntax would incorporate the “find” formula to pull the second “1”?
crystal clear explained.
Thank you Two Stones.
Hi. Looks like your formula works only when the rows are sorted, and picks up the next date. I can't use this.
Best video on finding same or next date using it for finding loading dates for ATM, thanks alot
Glad you liked it. Thank you, Nita.
Excellent challenge. Fascinating.
Thanks Oz.
Nice to see all the AMSTERDAM-people...
I hope, you enjoyed the fish ;-)
Hello, mate! This is a nice example. I would use AGGREGATE, because you don't need array-brackets with CTRL+SHIFT+ENTER. Also the date has not to be in a sorted list. Hope to see you next year in Amsterdam for some more beers.
Greetings from the continent, Andreas
=IFERROR(AGGREGATE(15,6,tbl_date[Date]/((tbl_date[Date]>=TODAY())*(tbl_date[class]=D3)),1),"no date availible")
Thanks Andreas. Yes hopefully we can meet again in Amsterdam next year.
Thank you for the tip.
hi alan, i have watched this many times and always find something i missed during the last watching! as usual, very well explained, but i am rather slow at grasping things at the moment.
i have used the formulas quite successfully in one of my projects, and i am quite satisfied however, i have the following scenario:
column a: classes, unsorted;
column b: dates sorted (oldest to newest).
this data is on sheet 1.
i would like the next date after today date, with appropriate class, too show up automatically on sheet 2.
i was asking myself - and doing lots of research on the wonderful youtube - if / how this could be possible.
i am not practising has much has i would like to on your vba course. hopefully have have more motivation during the holiday period.
regards from a cold berlin
colin
It's very cold here too Colin, though thankfully has stopped raining for now. I'll have a look.
Hi Colin, you would need a cell for the class and a cell for the dates. For the class cell you would have;
=INDEX(Sheet1!A2:A13,MATCH(1,(Sheet1!B2:B13>=TODAY())*1,0))
Make sure you press Ctrl + Shift + Enter.
For the date you would have;
=INDEX(Sheet1!B2:B13,MATCH(1,(Sheet1!B2:B13>=TODAY())*1,0))
Boss, I am unable to use this formula... I have similar query but not getting result.. Can you help
Excellent job.
What if I have an inventory card (Date, B. Balance, In, Out, and E. Balance), this card includes many transactions during the month, and I want to know the last occurrence in the E. Balance based on the last date in the date column.
Thanks Lotfy. It sounds like you could use the formula below. This assumes the dates are in A.
=VLOOKUP(MAX(A2:A13),A2:E13,5,FALSE)
Computergaga
Thanks a lot.
You're welcome Lotfy.
How can I make it so it doesn't need to look exactly for Power BI? As in, if I just typed Power or BI, it could still find the next date after today.
This formula will work Ruben.
=INDEX(A2:A13,MATCH(1,(A2:A13>=TODAY())*(N(ISNUMBER(FIND(D3,B2:B13)))),0))
It uses the ISNUMBER and FIND combination to check for a partial match (th-cam.com/video/0_IGpT0bYZY/w-d-xo.html) and then N function to convert the result to a 1 (th-cam.com/video/6tRVo89xpP0/w-d-xo.html).
This is not working
many thanks for sharing solution. I have a question what if we need to to get a thing other than date how can we do that?
If the table was larger than the one shown in the video, and was maybe 8 columns wide. The formula would be the same except the first array that we put into the INDEX function.
I selected the dates so that it would return the date. I could have selected D2:D13 or F2:F13 or whatever data I would want returned. The rest of the formula would be the same.
can you please tell me what array that would be instead of first array...
Yep. Did you expand my previous comment :)
In the video I selected A2:A13, but you can select any range you want (depending what you want from the table).
So if I had data in column E that I wanted I could select E2:E13. So it would be =INDEX(E2:E13,MATCH(......
you are not getting me. my question what if i need to get every 2nd data of index array..
for example
John 20
John 30
michael 30
John 40
I need to get 30 of John in lookup value....
Hi Haider, I have this video on using VLOOKUP to return the last occurrence of a value in a list - th-cam.com/video/QAZ3L6xbNJc/w-d-xo.html
It can easily be adapted to get the 2nd occurrence or whatever is needed.
How I can get past closest date ?
Interesting. I'll have a look.
I have a solution in this video Akshay - th-cam.com/video/IYkX99NtdXY/w-d-xo.html