INDEX function with multiple IFs

Options

Hello! I want to index in the "Mês atual" row the value that corresponds to the passed month and the actual year we´re on, so this column will just have one cell with value. I managed to index the month value, but it doesn't correspond to the right year, it's pulling it from the 2022 row. This is the formula:

=INDEX(IF(MONTH(TODAY()) - 1 = 1; JAN@row); MATCH(YEAR(TODAY()); ANO@row))

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @browning.m

    Are you looking to only bring the last month's data into that cell? Right now your formula is showing for January, but do you want it to pull February, since we're in March?

    If I'm understanding you correctly, try this:

    =INDEX(ANO1:DEZ9; MATCH(YEAR(TODAY()); ANO1:ANO9; 0); MONTH(TODAY()))

    The first range of the INDEX function should look across all of your cells, from the first cell in ANO to the last cell in DEZ.


    Then the MATCH function finds today's Year, and maps that to the year in the ANO column.

    Then at the end, to find the right column to bring back, we use "MONTH(TODAY())" which will bring back the month number of Today's date. However since our range has an additional column (Ano), this means that today's month (3) will bring back the 3rd column in this whole range, which is actually the previous month (Fev).

    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

  • browning.m
    Options

    @Genevieve P. The "ANO" column is going to have rows added throughout the years, so I can't restrain the gap, I changed the formula but it's saying there's no match, =INDEX(ANO@row:DEZ9; MATCH(YEAR(TODAY()); ANO1:ANO@row; 0); MONTH(TODAY())). The INDEX part is working just fine, but when I changed the MATCH it didn't work.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @browning.m

    If you want to adjust the range so it selects the entire column, you can take out the row references like so:

    ANO:DEZ

    Try this:

    =INDEX(ANO:DEZ; MATCH(YEAR(TODAY()); ANO:ANO; 0); MONTH(TODAY()))

  • browning.m
    Options

    @Genevieve P. That's perfect! Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Great! I'm glad this works for you 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!