IF with INDEX function

browning.m

I'm trying to elaborate a formula that only brings up a cell from a month of the year (ex: january) that is connected to the year we are at now (2023) and the year before (2022). So when we are in 2024, the cells of the months that come up are from the year 2024 and 2023, and etc. For this, I created a separate sheet, so this sheet in the image is just where the data is supposed to come from. I think I'll have to use the IF function and INDEX, but I don't know how.

Jeff Reisman

@browning.m

I would use an INDEX/COLLECT (multiple criteria) or INDEX/MATCH (single criteria) for this.

The format for INDEX/MATCH is:

INDEX({create reference to remote sheet column you want to collect value from}, MATCH(value from cell or static value, {create reference to remote sheet column you want to match value from}, 0))

Follow Smartsheet's prompts to create the remote references as you create your INDEX formula. Select the column header when doing so in order to make the entire column the range.

So your formula would look something like this:

=INDEX({Source sheet Jan column range}, MATCH("2022", {Source sheet YEAR column range}, 0))

Repeat for 2023.