Forum Discussion

Copper Contributor

Mar 10, 2022

Solved

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

excel

Formulas and Functions

OliverScheurich
Mar 11, 2022
joel70
=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.
next current date.pdf181 KB

PeterBartholomew1

Silver Contributor

Mar 11, 2022

joel70

= 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")
         )
      )
   )