How to Return the Latest Cloud Classification by Matching Name in WPS Office Spreadsheets
Question details
The user needs a formula in WPS Office Spreadsheets that finds a row in Sheet2 by exact name and returns the Cloud Classification from the row with the latest date for that name.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has data across two sheets: Sheet1 contains names and Sheet2 contains names, dates, and Cloud Classification values. They need to pull the classification for the most recent date per name.
- Observed behavior
- The goal is to retrieve the latest Cloud Classification dynamically instead of manually searching for the newest row for each name.
Confirm that Sheet1 has a list of names and Sheet2 contains the matching names, dates, and Cloud Classification columns. Identify the actual column letters for Name, Date, and Classification in your workbook before applying the formula.
Use a Single FILTER Formula to Return the Latest Cloud Classification by Name
Use this formula when your WPS Office Spreadsheets version supports dynamic array functions such as FILTER and MAXIFS; it returns the matching classification in one cell without manually checking dates.
This method first uses MAXIFS to find the latest date for the name in Sheet1!A2. The FILTER function then checks both the name and that latest date in Sheet2 and returns the value from the Cloud Classification column.
Click the first cell in the Cloud Classification column in Sheet1, for example B2, where you want the latest classification to appear.
Type =FILTER(Sheet2!$J$3:$J$9,(Sheet2!$H$3:$H$9=Sheet1!A2)*(Sheet2!$I$3:$I$9=MAXIFS(Sheet2!$I$3:$I$9,Sheet2!$H$3:$H$9,Sheet1!A2))) and press Enter.
Replace Sheet2!$J$3:$J$9 with your Classification column, Sheet2!$H$3:$H$9 with your Name column, Sheet2!$I$3:$I$9 with your Date column, and Sheet1!A2 with the first name cell.
Drag the fill handle down the column to apply the formula to the remaining names in Sheet1.
Use an INDEX and MATCH Array Formula for Older Spreadsheet Versions
Use this classic array formula if FILTER is not available in your WPS Office version and you need an alternative lookup method.
Use WPS Office Spreadsheets to Look Up the Latest Cloud Classification by Name
Download WPS Office to write the same formula in WPS Spreadsheets. It supports Excel-style lookup functions so you can match names, calculate the latest date with MAXIFS, and return the Cloud Classification without changing your existing workbook.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains Sheet1 and Sheet2.
- 2. Select the result column in Sheet1: Click the first cell where you want the latest Cloud Classification to appear, such as B2.
- 3. Enter the lookup formula: Type the FILTER or INDEX/MATCH formula using your Name, Date, and Cloud Classification columns in Sheet2.
- 4. Press Enter or Ctrl+Shift+Enter as required: Press Enter for FILTER or Ctrl+Shift+Enter for an array formula, depending on the method you choose.
- 5. Fill the formula down: Drag the fill handle down to apply the result to all name rows in Sheet1.
Frequently Asked Questions
Why does my FILTER formula return #CALC! when no match is found?
FILTER returns #CALC! when the criteria match no rows. Check that the name in Sheet1 is exactly the same as the Name column in Sheet2, including extra spaces, and that the date column contains real dates instead of text.
Can I use this formula if my Name, Date, and Cloud Classification columns are in different positions?
Yes. Replace the column references in the formula with the actual columns from Sheet2. For example, use H for Name, I for Date, and J for Classification, or change them to whatever columns your data uses.
Does this work in older versions of WPS Office Spreadsheets?
If FILTER is not available, use the INDEX/MATCH array formula with Ctrl+Shift+Enter. Update to the latest WPS Office version to access dynamic array functions such as FILTER and MAXIFS.
What should I do if there are duplicate names with the same latest date?
The formula may return multiple rows or the first matching row depending on the function. Add a unique identifier or ensure that each name has only one row per date so the lookup returns one classification.
