# Formula to look at varying number of cells and return latest date.

Copper Contributor

# Formula to look at varying number of cells and return latest date.

I have a large spreadsheet that lists order numbers in column A and the date that each parcel within that order was dock confirmed in column B.  I need to create a formula to look at the dates in column B associated with the order number and return the latest date.  There may only be one parcel in an order or there could be hundreds.  I was wanting to create a column C that would contain the formula next to the row with the order number, see below. Can anyone help?

3 Replies

# Re: Formula to look at varying number of cells and return latest date.

=IF(A2<>"",MAX(INDEX(\$B\$2:\$B\$26,ROW(B2)-1):INDEX(\$B\$2:\$B\$26,IFERROR(SMALL(IF(A2:\$A\$26<>"",ROW(A2:\$A\$26)-1),2)-1,COUNT(\$B\$2:\$B\$26)))),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

# Re: Formula to look at varying number of cells and return latest date.

@Debbie_Wood If you a Microsoft365 user then could try below dynamic array formula-

``````=LET(
i,SCAN(A2,A2:A16,LAMBDA(a,x,IF(x="",a,x))),
MAP(A2:A16,LAMBDA(t,IF(t="","",TAKE(SORT(FILTER(B2:B16,i=t),1,-1),1)))))``````

See the attached file.

# Re: Formula to look at varying number of cells and return latest date.

create temp table aa as
select *,fillna(`Order`) ord from last_date;
create temp table bb as
select ord,max(`Date`) latest_date from aa group by ord;
create temp table cc as
select * from aa left join bb on aa.`Order`=bb.ord;
select colExclude[\bord\b] from cc;