How to Return the Latest Date and Matching Rating in Excel
Question details
The user needs to automatically summarize data to find the most recent transaction date and its corresponding rating for specific country and state combinations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automatic data summary from a table containing transaction records with dates, countries, states, and ratings.
- Observed behavior
- The user wants a clean summary table that correctly extracts the maximum date for grouped criteria and the exact rating linked to that specific date.
Ensure your source data is formatted as an official Excel Table (e.g., 'Table1') so that formulas dynamically update when new records are added. Also, verify that your date column is formatted as actual dates rather than text.
Extract Latest Date and Rating Using MAXIFS and XLOOKUP
This dynamic formula approach is perfect when you want a highly customized summary table that updates automatically without needing to be refreshed.
By combining the MAXIFS function to find the most recent date with a multi-criteria XLOOKUP, you can precisely extract the exact rating associated with that date.
In your summary table (e.g., cell C2), enter the formula =MAXIFS(Table1[Date], Table1[Country], A2, Table1[State], B2). This evaluates the table and returns the most recent date for the specific Country in A2 and State in B2.
In the adjacent cell (e.g., D2), enter =XLOOKUP(1, (Table1[Country]=A2)*(Table1[State]=B2)*(Table1[Date]=C2), Table1[Rating], "Not found"). This formula uses boolean logic to match the country, state, and the newly found latest date to return the exact rating.
If multiple transactions occurred on the exact same latest date, the XLOOKUP function will return the first matching rating it finds. If you need to view all ratings for that specific date, replace XLOOKUP with the FILTER function.
Summarize Latest Dates and Ratings using a PivotTable
A PivotTable is an excellent no-formula alternative for quickly aggregating large datasets by category and finding maximum or minimum values.
Easily Analyze Data with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced dynamic array functions like XLOOKUP, MAXIFS, and powerful PivotTable features, allowing you to summarize complex data effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your transaction data file.
- 2. Format as a table: Select your data range and press Ctrl+T to format it as a table, naming it 'Table1' for easy formula referencing.
- 3. Apply the formulas: Use the MAXIFS and XLOOKUP formulas exactly as you would in Excel to extract your latest dates and corresponding ratings.
- 4. Use PivotTables: Alternatively, navigate to Insert > PivotTable to quickly build a tabular summary without writing a single formula.
Frequently Asked Questions
Why does my MAXIFS formula return a 0 or an error?
This usually happens if the criteria ranges and the max range are not the exact same size, or if the date column contains text instead of actual date values. Ensure all ranges in your MAXIFS formula match in length and that your dates are formatted properly.
Can I use VLOOKUP instead of XLOOKUP for this task?
VLOOKUP cannot easily evaluate multiple criteria (like Country, State, and Date simultaneously) without creating a helper column that concatenates these values in your source data. XLOOKUP or an INDEX/MATCH combination is much more efficient for multi-criteria lookups.
How do I format the PivotTable to look like a normal data table?
Under the PivotTable Design tab on the ribbon, select 'Report Layout' > 'Show in Tabular Form', then click 'Repeat All Item Labels'. Finally, turn off Subtotals and Grand Totals to make the PivotTable look like a standard flat table.
What if there are multiple ratings on the exact same latest date?
By default, XLOOKUP will return the first match it finds from the top down. If you want to extract all ratings recorded on that specific date, replace the XLOOKUP formula with the FILTER function: =FILTER(Table1[Rating], (Table1[Country]=A2)*(Table1[State]=B2)*(Table1[Date]=C2)).
