Forum Discussion
Xlookup - next current date
Hi Guys,
I am looking for how to pull in the next most recent date with an Xlookup.
I have 2 columns, 1 with project numbers and then another column with the date.
How can I get Xlookup to bring up the next recent date - example below:
Project Number Date
12345 8/20/21
12345 11/31/21 - want this date to pull up in the formula
12345 2/1/2022
2201 9/22/21
2201 12/22/21
2201 3/22/22
I am using excel in office 365
Thank you for your help.
=INDEX($B$1:$B$24,LARGE(IF($A$1:$A$24=D2,ROW($1:$24)),IF(SUMPRODUCT(N($A$1:$A$24=D2))>1,2,1)))An alternative could be above formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
4 Replies
= BYROW(UNIQUE(ProjectNum), LAMBDA(p,LARGE(FILTER(Date,ProjectNum=p),2)))or eliminating #NUM! errors
= BYROW(UNIQUE(ProjectNum), LAMBDA(p, LET( projectDates, FILTER(Date,ProjectNum=p), countDates, ROWS(projectDates), IF(countDates>1, LARGE(projectDates,2), "Not available") ) ) )=INDEX($B$1:$B$24,LARGE(IF($A$1:$A$24=D2,ROW($1:$24)),IF(SUMPRODUCT(N($A$1:$A$24=D2))>1,2,1)))An alternative could be above formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
You'll want to use FILTER instead of XLOOKUP. The COUNTIF will prevent errors if there are not 2+ occurrences of a given Project#.
