Pull latest amount from a column based on date column

I have a sheet that my Finance team updates every week with an update request and would like to pull the latest update amounts for the different bank accounts into an overview sheet for the executive team.

For example, in the screenshot below, I want to pull the 2/24/25 update for the Operating Account, 2/10/25 update for the Chase Reserve Account, etc. I want the formula to automatically pull the latest update when the next week is entered. Is there a way to do this?

Find more posts tagged with

Formulas

Accepted answers

Isis Taylor

Hi @alys

This should work if you are using a cross sheet reference.

=INDEX({Operating}, MATCH(MAX(COLLECT({Week}, {Operating}, <>"")), {Week}, 0))

All comments

Isis Taylor

Hi @alys

This should work if you are using a cross sheet reference.

=INDEX({Operating}, MATCH(MAX(COLLECT({Week}, {Operating}, <>"")), {Week}, 0))

alys

@Isis Taylor That worked, thank you so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.