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))
Answers
-
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 <> ""))
-
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.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!