How to find the value of one cell from looking at three columns

Hi everyone I am trying to create a formula where it utilizes three columns to pull a value of one cell from the row.

Ultimately what I am trying to accomplish is create an formula where there are three columns in a report (Year, Month, and Average Amount)

Every month a new row will be added and a formula I am trying to create a formula in a new cell to feed into a dashboard

The Formula will first look for the specific year so for example 2023, and then it will look for the most current month which would be numbered through 1-12. After it finds the row with the most current month it will pull the value from the Average Amount Column every time a new month is added is there a formula that can do this?

This is what I currently have so far

=IF((([year],2023) AND if([month], 11)), [Average_Amount]@[month]row))

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

If new rows are being added to the bottom of the sheet, you can use:

=INDEX([Average_Amount]:[Average_Amount], COUNTIFS([Average_Amount]:[Average_Amount], @cell <> ""))

Kleerfyre

So you are going to need a helper cell. I suggest to put it in the summary and call it today and just put this formula in it:

=TODAY()

Then you will need to reference that cell in your formula:

=IF(AND(Year@row="2023",Month@row=MONTH(Summary Cell)),[Average_Amount]@row)

You don't need brackets around any column that is a single word.

Not 100% sure that formula will work, but this will get you on a better path.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.