IF and INDEX functions
Hello! I want to call a month cell that's connected to the year we're on (2023) and the past year (2022) but automatically as the years go by. Example, in 2023 I want to know the number that's in the cell that corresponds to that row (january, february...) and also that same information but from the year before (2022). For this, I have created another sheet that brings data from the one in the image below. I think I'll need to have a IF function to add a =YEAR(TODAY()) and a INDEX to pick up the cell, but I'm not sure.
Answers
-
You would need a helper column on the sheet in the screenshot that contains just the year.
Then you would use a formula similar to...
=INDEX({All month columns}, MATCH(YEAR(TODAY()), {Year Helper Column}, 0), MONTH(TODAY()))
-
Thanks @Paul Newcome !!
For the year before I used =INDEX({All month columns}, MATCH(YEAR(TODAY(-365)), {Year Helper Column}, 0), MONTH(TODAY())). It works, but is there maybe a easier or simpler way to make that reference?
-
There is a slim window where that could be incorrect every 4 years (leap years). Instead of pulling the year from 365 days in the past, it would be slightly more accurate to subtract 1 from the current year.
YEAR(TODAY()) - 1
-
@Paul Newcome Thank you!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!