IF with INDEX function

browning.m ✭✭
edited 03/03/23 in Formulas and Functions

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
    Jeff Reisman ✭✭✭✭✭✭


    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.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!