Function Problems

How to Find the Year with the Maximum Value in Excel

Adam Davis Oct 7, 2026 869 views

Question details

The user needs to construct a formula to identify which year's worksheet contains the maximum value within a dataset spanning multiple years.

How to Find the Year with the Maximum Value in Excel

Product: Excel
Device & OS: not provided
Scenario: Comparing daily or monthly recorded data across multiple worksheets, where each worksheet represents a different year.
Observed behavior: Comparing identical row numbers across sheets causes data misalignment after February 29th in leap years, making simple 3D references unreliable without proper date matching.

Before you start

Ensure that your worksheets are clearly named by their respective years (e.g., '2019', '2020') and verify that the data ranges you plan to compare are formatted as numbers.

Solution 1Recommended

Consolidate Data and Use MAX with XLOOKUP

Combining your yearly data into a single master table is the most robust method, allowing for accurate date comparisons regardless of leap year discrepancies.

Leap years introduce February 29th, meaning dates are no longer aligned on the same row across different sheets after March 1st. Consolidating the data prevents these row-shifting errors.

Create a master sheet

Consolidate your data from all the individual year worksheets into one master table with dedicated columns for 'Date', 'Value', and 'Year'.

Find the maximum value

In a blank cell, type the formula =MAX(B:B) (assuming column B contains your values) to identify the highest number in the dataset.

Retrieve the corresponding year

In an adjacent cell, use the formula =XLOOKUP(MAX(B:B), B:B, C:C) (assuming column C contains the year) to return the year associated with the maximum value.

Consolidate Data and Use MAX with XLOOKUP

Data Alignment: This approach eliminates row misalignment caused by leap years by relying on actual data records rather than static row positions.

Solution 2

Use a 3D Reference and IFS Function Across Worksheets

Use this method if you cannot consolidate your data and need to compare specific cells across adjacent identically-structured worksheets.

Analyze Data Efficiently

Easily Manage Multi-Sheet Data in WPS Spreadsheet

WPS Office provides powerful functions like MAX, XLOOKUP, and IFS natively, making it easy to analyze complex datasets across multiple worksheets without compatibility issues.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your yearly data worksheets.
2. Enter the 3D MAX formula: Select a blank cell in your summary sheet and type =MAX('2019:2024'!A2) to find the highest value.
3. Apply lookup formulas: Use WPS's built-in XLOOKUP or IFS functions to map the maximum result back to its corresponding year sheet.

Fully compatible with Microsoft Excel formulas and functionsAdvanced cross-sheet 3D referencing capabilitiesBuilt-in XLOOKUP and IFS for complex data retrievalLightweight application with high processing speed

microsoft office alternative - wps office

Frequently Asked Questions

Why do my cross-sheet formulas break during leap years?

Leap years contain February 29, which adds an extra row to daily data sheets. This means data from March 1 onwards will be shifted by one row compared to non-leap years, causing direct row-by-row comparisons to mismatch.

What happens if there are duplicate maximum values in different years?

When using standard lookup functions like VLOOKUP, XLOOKUP, or IFS to match the maximum value, the spreadsheet will return the first matching year it encounters based on the order defined in your formula.

What is a 3D reference in a spreadsheet?

A 3D reference refers to the same cell or range on multiple adjacent worksheets. For example, '2019:2024'!A2 calculates the data in cell A2 across all the sheets placed between 2019 and 2024 inclusive.

Excel & Data Problems