Calculation Issues

How to Calculate Dynamic Current-Year and Prior-Year YTD in Excel

Elise Williams Oct 10, 2026 869 views

Question details

The user needs to calculate dynamic Year-to-Date (YTD) totals for both the current and prior year based on a dynamically selected product and month.

How to Calculate Dynamic Current-Year and Prior-Year YTD in Excel

Product: Excel
Device & OS: not provided
Scenario: Building a dynamic financial, sales, or operational report where the YTD totals must automatically recalculate when the user changes the reporting month or product item from a dropdown.
Observed behavior: The YTD calculation successfully returns the total sum spanning from January to the selected month for the specified product, updating dynamically rather than relying on static cell ranges.

Before you start

Ensure your dataset is organized in a matrix format with product names in a single column and months clearly labeled across the header row. Also, verify that your spreadsheet software supports the XLOOKUP function (available in newer versions like Microsoft 365, Excel 2021, and WPS Office).

Solution 1Recommended

Use SUM and XLOOKUP to Create a Dynamic YTD Formula

By nesting XLOOKUP inside a SUM function, you can dynamically define the start and end boundaries of your summing range based on a selected product and month.

The colon (:) operator in Excel is typically used between static cell references like A1:A5. However, XLOOKUP returns a cell reference, meaning you can place a colon between two XLOOKUP functions to dynamically generate a range. The first XLOOKUP finds the January value for the chosen product, and the second nested XLOOKUP finds the value for the selected month.

Define the target inputs

Designate specific cells for your dynamic inputs. For example, use cell D9 for the product name you want to look up, and Sheet2!E6 for the target month you want the YTD to end on.

Set the starting boundary (January)

Create the first part of the formula to find the product and return its January column value: XLOOKUP(D9, Sheet1!$A$2:$A$5, Sheet1!$B$2:$B$5). Here, column A contains the product names and column B contains the January data.

Set the dynamic ending boundary

Create the second part of the formula to find the target month. Use a nested lookup: XLOOKUP(D9, Sheet1!$A$2:$A$5, XLOOKUP(Sheet2!$E$6, Sheet1!$B$1:$S$1, Sheet1!$B$2:$S$5)). The inner XLOOKUP finds the correct month column, and the outer XLOOKUP finds the product row.

Combine with the SUM function

Join the two boundaries with a colon inside a SUM function. Enter this in your result cell: =SUM(XLOOKUP(D9,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5):XLOOKUP(D9,Sheet1!$A$2:$A$5,XLOOKUP(Sheet2!$E$6,Sheet1!$B$1:$S$1,Sheet1!$B$2:$S$5))).

Adjust for Prior-Year YTD

To calculate the prior-year YTD, duplicate the formula in a new cell (e.g., F9), but change the range references to point to your prior-year data blocks (for instance, changing January from column B to column N, and adjusting the target month cell).

Use SUM and XLOOKUP to Create a Dynamic YTD Formula

Formula Adaptability: Ensure you lock your data ranges with absolute references ($) so the formula continues to work perfectly if you drag or copy it to other cells in your dashboard.

Advanced Spreadsheets

Calculate Dynamic YTD Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions including XLOOKUP, allowing you to easily build dynamic YTD reports and complex financial models without switching software.

1. Open your report in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the product and monthly data.
2. Select the YTD result cell: Click on the cell where you want the dynamic current-year or prior-year total to be displayed.
3. Input the dynamic formula: Type the formula =SUM(XLOOKUP(...):XLOOKUP(...)) mapping exactly to your data ranges as described in the solution.
4. Test the dynamic behavior: Press Enter, then change the target month in your input cell to watch the WPS Spreadsheet instantly recalculate the YTD totals.

Native support for modern formulas like XLOOKUP, UNIQUE, and FILTERSeamless format compatibility with Microsoft Excel (.xlsx and .xls)High-performance calculation engine for large datasetsIntuitive and free-to-use interface for all your data analysis needs

Frequently Asked Questions

Why does my XLOOKUP formula return a #NAME? error?

The #NAME? error typically occurs if you are using an older version of Excel that does not support the XLOOKUP function (such as Excel 2016 or 2019). To fix this, you either need to upgrade your software to a newer version like Microsoft 365 or WPS Office, or use a combination of INDEX and MATCH functions instead.

Can I use INDEX and MATCH instead of XLOOKUP for dynamic YTD?

Yes. If XLOOKUP is unavailable, you can use the INDEX and MATCH combination. A typical dynamic range formula looks like this: =SUM(INDEX(DataRange, MATCH(Product, ProductList, 0), 1):INDEX(DataRange, MATCH(Product, ProductList, 0), MATCH(Month, MonthList, 0))).

How do I handle missing data or #N/A errors in the YTD calculation?

If a product or month is missing from your dataset, XLOOKUP will return an error. You can handle this gracefully by wrapping your entire SUM formula in the IFERROR function, like =IFERROR(SUM(...), 0), which will return a 0 instead of breaking your report.

Excel & Data Problems