Does Excel Formula Exist to Output Same Period Last Year?

Hi folks,

New here!

I'm trying to figure out how to do something using an Excel formula which I would expect is not difficult but cannot find an answer anywhere. I understand DAX formulas exist if you use PowerPivot and I've done that in the past but need to figure out an old school classic formula approach!

See below example of sales in Q3 last year and current year with totals and YoY growth. At the end of each month you would have to manually update the formula in highlighted cell to reflect the prior year period in order to compare apples to apples. How could I automate this with a formula?

For example, once you have Sales for Jul/Aug 2018, it would output the revenue for Jul/Aug 2017, so that you can see the respective YoY growth.

Same Period Last Year.PNG

Thanks in advance!

Zahid

Hello and welcome to the forum.

It'll be hard to give you a working solution just based off of a picture. It would be much better for you to upload the sample workbook.

That being said, what formula is being used for the Current Period cell (cell G5?)?

It looks like your dates are actually text values (left justification) so that presents another challenge.

Try this:

=INDEX(A5:F5,MATCH(TEXT(DATE(LEFT(INDEX(A4:F4,MATCH(G5,A5:F5,0)),4)-1,RIGHT(INDEX(A4:F4,MATCH(G5,A5:F5,0)),2),1),"yyy-mm"),A4:F4,0))

Thank you both for your v quick responses. Appreciate it!

AliGW - I tried your formula but got an error.

63falcondude - My dates were text and your suggestion worked but when I tried adding data to the 2018-08 column it didn't work as expected. I've since switched to date format and tried to attach file as suggested but the forum won't let me attach as I am new. Would you mind updating the formula to reflect date instead of text format? I'll then troubleshoot from there.

I think I've managed to attach a file now.

Looking at the sample in post #5, try this in H5:

=INDEX(A5:F5,MATCH(DATE(YEAR(INDEX(A4:F4,MATCH(G5,A5:F5,0)))-1,MONTH(INDEX(A4:F4,MATCH(G5,A5:F5,0))),1),A4:F4,0))

Also, I notice there is not a formula in G5. If you want one there too, you can try this:

=LOOKUP(2,1/(A5:F5<>0),A5:F5)

This returns the right-most non-zero value from A5:F5.

Thank you you're so helpful. I've tested and realised I may have not explained clearly my desired outcome. I do not want the totals to reflect the most current month but rather total sales for period to date. So if I have data for Jul/Aug-18, I want the total of that to appear in G5 and the total of Jul/Aug-17 in H5. Does that make sense? Sorry for confusion!

I do not want the totals to reflect the most current month but rather total sales for period to date.

That's actually easier then.

Try these:

G5 =SUM(D5:F5)

H5 =SUMIF(D5:F5,"<>0",A5:C5)

Wow folks I'm learning so fast trying all these formulas thank you so so much!! I'm super happy to say that JohnMPL's cumulative formula did the trick and is exactly what I needed. I actually spent half a day playing with the indirect formula using mapping tables thinking there's no simple way to do this but you proved me wrong!!

I think I'll coming to this forum quite often!!

Thanks again!