# Return Value Based on Latest Date

Copper Contributor

# 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

7 Replies

# Re: Return Value Based on Latest Date

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

# Re: Return Value Based on Latest Date

Sorry I should have mentioned, I don't want to use the latest date as criteria in your D2 preferably not an array either

# Re: Return Value Based on Latest Date

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?

# Re: 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.

# Re: Return Value Based on Latest Date

@djclements I would like it to include dates that only appear once also

# Re: Return Value Based on Latest Date

So when entering new dates with balances, regardless if single or duplicate dates which it can handle, it would return the latest balance. I will always be entering dates oldest to newest/latest. Unfortunately none of the examples attached previously show what I'm after

# Re: Return Value Based on Latest Date

@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)``````