INDEX function with multiple IFs

browning.m

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

Captura de Tela (34).png

Genevieve P.

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

Genevieve P.

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

@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.

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

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

Genevieve P.

Great! I'm glad this works for you 🙂