IF and INDEX functions

browning.m

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.

Paul Newcome

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()))

browning.m

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?

Paul Newcome

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

browning.m

@Paul Newcome Thank you!!

Paul Newcome

Happy to help. 👍️