Forum Discussion
Return Value Based on Latest Date
Hello, I'm trying to return a value based on the latest date which has duplicates
For example...
| Date | Balance |
| 01/01/24 | £100 |
| 02/01/24 | £120 |
| 01/02/24 | £200 |
| 01/03/24 | £210 |
| 01/03/24 | £220 |
| 01/03/24 | £230 |
Date DD/MM format
As there can be multiple transactions on the same day
So I'm trying to return the last value £230
I've looked online but couldn't find a clear enough solution
Office Pro Plus 2021
Please advise & thank you for your time
=INDEX($B$2:$B$7,LARGE(IF($A$2:$A$7=D2,ROW($A$2:$A$7)-1),1))
This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
- Sorry I should have mentioned, I don't want to use the latest date as criteria in your D2 preferably not an array either
I'm unsure if i correctly understand what you want to do. Perhaps the suggestions in the attached sample file are helpful. Otherwise could you add a few examples along with the expected result?
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)
