Prior Month Month/Year Formula Crossing Over 2 Years

Allison Horn

@Paul Newcome - I have tried just about everything and cannot get the formula to work.

Paul Newcome

Which error are you getting?

Are you able to create a copy of the sheet, replace sensitive data with mock data, publish it, then share the link? It might be helpful if I could work in it directly.

Allison Horn

Yes, see below. I highlighted the cell in Green that is giving me the issue. THANK YOU!

https://app.smartsheet.com/b/publish?EQBCT=5de0af88df61481b840bd5b7b8298a3c

Paul Newcome

Ugh. January:December is multiple columns whereas the rest of our ranges in the COLLECT function are single columns. I should have known better than to try that. Sorry. That was my mistake. Back to the drawing board...

Paul Newcome

I just plugged this in and it seems to be working...

=INDEX(January:December, MATCH(Metric@row + " " + RIGHT(INDEX(ANCESTORS(), 1), 4), [Index Match Lookup]:[Index Match Lookup], 0), IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "February", 2)))

Allison Horn

@Paul Newcome - Not sure where your last thread went, but thank you for jumping in. If we look at prior year, it's still not functioning. Since we are now in February (compared to when I originally posted) the Prior Month 2 is now December 2022 where the hiccup is.

https://app.smartsheet.com/b/publish?EQBCT=5de0af88df61481b840bd5b7b8298a3c

Paul Newcome

Try finishing out the nested IFs for the month text. I just added a 3rd one in for "December" and it seems to be working.

Allison Horn

That did it! You are a genius, thank you so much for all of your help.

Here is the final formula in case anyone else needs it.

=INDEX(January:December, MATCH(Metric11 + " " + RIGHT(INDEX(ANCESTORS(), 1), 4), [Index Match Lookup]:[Index Match Lookup], 0), IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "January", 1, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "February", 2, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "March", 3, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "April", 4, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "May", 5, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "June", 6, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "July", 7, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "August", 8, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "September", 9, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "October", 10, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "November", 11, IF(LEFT(INDEX(ANCESTORS(), 1), LEN(INDEX(ANCESTORS(), 1)) - 5) = "December", 12))))

Paul Newcome

Excellent. The reason I stopped at Feb was to just test the more complex portions of the formula without cluttering things up with a (relatively) straightforward nested IF.

Happy to help. 👍️