How to Look Up the Most Recent Year and Matching Value in WPS Office Spreadsheets
Question details
Look up the most recent year for records that match an indicator, age group, and country ISO code, and return the corresponding value from another column in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a multi-column dataset of country or indicator records and needs the latest annual value for a selected indicator, age group, and country ISO code.
- Observed behavior
- The dataset contains multiple years per record group and the user needs an automated formula to find the maximum year and return its matching value.
Make sure the year column contains numeric values, not text. Confirm the column positions for indicator, age group, country ISO code, year, and the value to return are consistent across the data range.
Use LET, FILTER, CHOOSECOLS, and XLOOKUP to return the latest matching value
Best for modern WPS Office dynamic arrays; it filters matching rows and returns the value associated with the maximum year in one formula.
This formula assumes the data is in A2:L1000, the indicator is in column D, age group is in column F, country ISO code is in column H, year is the 10th column in A:L, and the return value is column L, the 12th column. Adjust the ranges and column indexes to match your workbook.
Click the cell where you want the latest matching value to appear.
Type =LET(rows,FILTER(A2:L1000,(D2:D1000=indicator)*(F2:F1000=age_group)*(H2:H1000=country)),years,CHOOSECOLS(rows,10),values,CHOOSECOLS(rows,12),XLOOKUP(MAX(years),years,values)) and replace indicator, age_group, and country with your cell references or defined names.
Press Enter and check that the returned value matches the latest year row for the selected indicator, age group, and country ISO code.
Use INDEX, MATCH, MAX, and IF for a compatibility formula
Use this array formula when LET, FILTER, or CHOOSECOLS is not available or when you need a non-dynamic formula for an older WPS Office version.
Use WPS Office Spreadsheets to Build a Latest-Year Lookup
WPS Office Spreadsheets supports the FILTER, LET, CHOOSECOLS, and XLOOKUP functions, so you can build a dynamic formula that filters by indicator, age group, and country ISO code, finds the maximum year, and returns the corresponding value.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the .xlsx or .xls file that contains your indicator, age group, country ISO code, year, and value columns.
- 2. Prepare the criteria input cells: Add or select three cells for indicator, age group, and country ISO code so the formula can reference them.
- 3. Enter the latest-year lookup formula: In an empty result cell, enter the LET/FILTER/CHOOSECOLS/XLOOKUP formula and adjust A2:L1000, 10, and 12 to match your actual data columns.
- 4. Test with known criteria: Enter a known indicator, age group, and country ISO code, then verify the formula returns the value from the most recent year.
Frequently Asked Questions
What if my year values are stored as text?
Convert them to numbers first, for example with the VALUE function or Text to Columns. Use a helper column if necessary, because MAX and CHOOSECOLS compare numeric values.
Can I use this formula in older versions of WPS Office?
Dynamic array functions such as FILTER, CHOOSECOLS, and LET require a modern WPS Office version or Microsoft 365. If they are not available, use the INDEX, MATCH, MAX, and IF array formula instead.
How do I change the return column in the formula?
Adjust the CHOOSECOLS column index. In the example, 10 is the year column within A:L and 12 is the value column; change 12 to the relative position of the column you want to return.
Does WPS Office support XLOOKUP?
Yes. WPS Office Spreadsheets supports XLOOKUP in modern versions and can open workbooks that already use it.
Can the criteria be cell references instead of defined names?
Yes. Replace indicator, age_group, and country with actual cell references such as $M$2, $M$3, and $M$4.
