Calculation Issues

Calculate Year-over-Year Percentage Change in Excel with XLOOKUP

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Calculate percentage change between the current and previous year for matching monthly or year-to-date periods.

Calculate Year-over-Year Percentage Change in Excel with XLOOKUP
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.
Before you start

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.

Solution 1Recommended

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.

1
Name the table

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

2
Enter the formula

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

3
Format the results

Select the results column and choose Home > Percentage Style. Adjust the decimal places using the buttons in the Number group.

Match the prior-year value with XLOOKUP
Missing or zero prior-year values: The formula returns a blank when the prior-year match is missing or its sales value is zero. XLOOKUP returns the first match if duplicate keys exist.
Calculate in WPS Spreadsheet

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. 1. Open your workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 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. 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. 4. Format and save: Select column E, choose Home > Percentage Style, and save the workbook in XLSX format.
Compare matching monthly or year-to-date sales periodsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface and formula bar
microsoft office alternative - wps office

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.