Forum Discussion
Return Value Based on Latest Date
Adam08320 Here's a couple of options using XLOOKUP with the optional [search_mode] argument set to -1 (last-to-first):
=LET(
data, A2:B7,
dates, INDEX(data,, 1),
latest_duplicate, MAX(FILTER(dates, COUNTIF(dates, dates) > 1, 0)),
XLOOKUP(latest_duplicate, dates, INDEX(data,, 2), 0,, -1)
)
- OR -
=LET(
data, A2:B7,
dates, INDEX(data,, 1),
only_once, UNIQUE(dates,, 1),
latest_duplicate, IF(ISNUMBER(ROWS(only_once)), MAX(FILTER(dates, ISNA(XMATCH(dates, only_once)), 0)), MAX(dates)),
XLOOKUP(latest_duplicate, dates, INDEX(data,, 2), 0,, -1)
)
Adjust the data range as needed (assuming Date is column 1 and Balance is column 2).
Based on my understanding of your description, these formulas will return the "last value" for the "latest date which has duplicates", so dates appearing only once will be ignored.
According to Microsoft Support documentation found online, all of the functions used in the above methods should be compatible with Excel 2021.
djclements I would like it to include dates that only appear once also
Adam08320 Unfortunately, it's not very clear what you're asking (even less so now). Are you just trying to return the last value in the Balance column? So, if Date was in column A and Balance was in column B, would any of the following do what you what?
=INDEX(FILTER(B:B, NOT(ISBLANK(A:A))), COUNTA(A:A)) =INDEX(B:B, XMATCH(, A:A) - 1) =XLOOKUP(MAX(A:A), A:A, B:B,,, -1)
