How to Find the Latest Account Update with MAXIFS in WPS Office Spreadsheets
Question details
The user needs to identify the latest update date and time for accounts that appear multiple times, because VLOOKUP may return only the first matching record.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- An account list contains duplicate account numbers with different update dates and times, and the user needs the latest update for each account.
- Observed behavior
- VLOOKUP can return an earlier first-match record, while MAXIFS returns the maximum date and time value for all rows belonging to the same account.
Make sure the update column contains real date/time values rather than text, and confirm that account numbers are formatted consistently. Decide the exact row range for your data so you can replace the sample reference $C$2:$C$100 and $B$2:$B$100 with your own range.
Use MAXIFS to Return the Latest Date and Time for Each Account
Use this method when your version of WPS Office Spreadsheets supports MAXIFS and you want a straightforward formula to find the latest update per account.
MAXIFS calculates the maximum value in the update date/time range when the corresponding account range matches the account in the current row. It works even when account numbers are repeated across many rows.
In a new column, select cell D2 next to the first row of your account data.
Type =MAXIFS($C$2:$C$100,$B$2:$B$100,B2), where column C contains update dates and times and column B contains account numbers.
Press Enter, then drag the fill handle from D2 down through the remaining data rows.
Compare the returned values with the source rows to confirm that each account shows its latest date and time.
Use a MAX and IF Array Formula for Older Spreadsheet Versions
Use this method if MAXIFS is not available in your spreadsheet version, because it combines MAX and IF to calculate the same result for older engines.
Find the Latest Account Update Easily with WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the MAXIFS formula to return the latest update date and time for each repeated account. The formula updates automatically when source data changes, so you do not need to manually sort and compare rows.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the file that contains the repeated account records.
- 2. Insert a helper column: Add a column named Latest Update next to the account and update date/time columns.
- 3. Enter the MAXIFS formula: In the first data cell of the helper column, enter =MAXIFS($C$2:$C$100,$B$2:$B$100,B2).
- 4. Fill the formula down: Press Enter and drag the fill handle down to apply the calculation to every account row.
- 5. Review the latest records: Sort or filter by the Latest Update column to quickly identify the most recent record for each account.
Frequently Asked Questions
Does MAXIFS work in WPS Office Spreadsheets?
Yes, current WPS Office Spreadsheets versions support MAXIFS. If your version does not have MAXIFS, use the MAX with IF array formula =MAX(IF($B$2:$B$100=B2,$C$2:$C$100)) instead.
Why does VLOOKUP not show the latest account update?
VLOOKUP returns the first matching value it finds, not the maximum date and time. MAXIFS or a MAX IF array formula evaluates all matching account rows and returns the latest value.
What format should the update date and time column use?
Use a real date/time or serial number format. If the values are stored as text, MAXIFS may not calculate them correctly, and you should convert them to a recognized date/time format first.
Can MAXIFS return the latest record with multiple conditions?
Yes, add extra criteria range and criteria pairs. For example, to match both account and region, use =MAXIFS($C$2:$C$100,$B$2:$B$100,B2,$A$2:$A$100,A2).
Can I use MAXIFS to return the latest text entry?
No, MAXIFS works with numeric values, including dates and times. For text values, you need a lookup method that sorts or compares text order differently.
