Calculate Year-over-Year Percentage Change in Excel with XLOOKUP
Question details
Calculate percentage change between the current and previous year for matching monthly or year-to-date periods.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains sales values by year, period type, and period, and the user wants to compare each value with the corresponding prior-year value.
- Observed behavior
- Each result should show the percentage change for the same period and period type in the previous year.
Check that years are numeric and that period types and period labels match consistently across years. Ensure each year, period type, and period combination identifies one sales value.
Match the prior-year value with XLOOKUP
Use this method in an Excel version that supports XLOOKUP and LET, especially when your data is in a table.
The formula looks up the previous year using the same MTD/YTD and Period values, then divides the difference by the prior-year sales value.
Select a cell in your Excel table, open Table Design, and set the Table Name to SalesData. Confirm the column names are YEAR, MTD/YTD, Period, and Sales(mEU).
Add a results column to the table and enter: =LET(lastYr,XLOOKUP(([@YEAR]-1)&"|"&[@[MTD/YTD]]&"|"&[@Period],SalesData[YEAR]&"|"&SalesData[MTD/YTD]&"|"&SalesData[Period],SalesData[Sales(mEU)],""),IF(OR(lastYr="",lastYr=0),"",([@[Sales(mEU)]]-lastYr)/lastYr))
Select the results column and choose Home > Percentage Style. Adjust the decimal places using the buttons in the Number group.

Calculate the change with SUMIFS
Use SUMIFS as an alternative when XLOOKUP is unavailable and each matching period has one sales value.
Calculate year-over-year change in WPS Spreadsheet
Use SUMIFS in WPS Spreadsheet to match the previous year's period and calculate its percentage change.
- 1. Open your workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Check the data layout: Arrange year in column A, period type in B, period in C, and sales in D, starting on row 2. Ensure each matching period has one sales value.
- 3. Calculate the change: In E2, enter =IFERROR((D2-SUMIFS($D$2:$D$100,$A$2:$A$100,A2-1,$B$2:$B$100,B2,$C$2:$C$100,C2))/SUMIFS($D$2:$D$100,$A$2:$A$100,A2-1,$B$2:$B$100,B2,$C$2:$C$100,C2),"") and drag the fill handle down. Extend the row limits if needed.
- 4. Format and save: Select column E, choose Home > Percentage Style, and save the workbook in XLSX format.

Frequently Asked Questions
What is the year-over-year percentage change formula?
Subtract the prior-year value from the current-year value, then divide by the prior-year value: (current year - prior year) / prior year. Format the result as a percentage.
How do I match monthly and year-to-date results separately?
Include both the period type and period in the XLOOKUP key or SUMIFS criteria. This prevents an MTD value from being matched with a YTD value for the same period.
Why is my result blank when the prior-year value is zero?
A percentage change cannot be calculated by dividing by zero. The formulas above return a blank for a zero or missing prior-year value.
What happens if there are duplicate prior-year records?
XLOOKUP returns the first matching record, while SUMIFS adds all matching values. Check the year, period type, and period columns for duplicates before choosing a method.




