How to Use the Excel LARGE Formula to Return Top Values for a Matching Date in WPS Spreadsheets
Question details
The user needs an Excel-compatible formula in WPS Spreadsheets to return the first, second, or third largest value when a date header matches a selected date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains a table with dates as column headers and numeric values in rows beneath each date. The user wants to pull ranked top values for a selected date into a summary sheet.
- Observed behavior
- The user is looking for a reliable formula combination such as LARGE plus FILTER to extract the top 1, 2, or 3 values for a matching date, and optionally return the corresponding item description.
Confirm that your data uses date values in one header row and numeric values in the rows below, and that the selected date is entered in a separate cell for the formula to reference.
Use LARGE with FILTER to Return the Nth Largest Value for a Selected Date
Best when your WPS Spreadsheets version supports the FILTER function and you need a dynamic formula that automatically updates when the selected date changes.
The FILTER function returns only the columns whose date header equals the selected date. LARGE then ranks those filtered numbers without a helper column.
Type the target date in a summary cell, for example Resumo!B5, so the formula can compare every date header against it.
Open the WPS Spreadsheets workbook and select the cell where the first largest value should appear.
Type =LARGE(FILTER(Folha4!$B$3:$M$106,Folha4!$B$1:$M$1=Resumo!B5),1) and press Enter.
Copy the formula and change the last argument from 1 to 2, 3, and so on for subsequent rankings.
Return the Item Description for a Ranked Value with XLOOKUP
Use this after you have the ranked value if you also need the item name or description from the same row, and your WPS version supports XLOOKUP.
Use an IF Array Formula when FILTER Is Not Available
Use this fallback for older WPS Spreadsheets versions or when the FILTER function is missing from your formula list.
Return Top Values by Matching Date in WPS Spreadsheets
Open your Excel workbook in WPS Spreadsheets, enter the LARGE and FILTER formula, and rank values for the selected date without switching tools. WPS Spreadsheets uses Excel-compatible formulas and saves .xlsx files natively.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx file that contains the date headers and numeric value rows.
- 2. Enter the selected date: Type the target date in a summary cell such as Resumo!B5.
- 3. Enter the LARGE and FILTER formula: In the result cell, type =LARGE(FILTER(Folha4!$B$3:$M$106,Folha4!$B$1:$M$1=Resumo!B5),1) and press Enter.
- 4. Add ranks 2 and 3: Copy the formula down or across and change the rank number to 2, 3, and so on.
- 5. Save the workbook: Save the file as .xlsx to keep working in WPS Spreadsheets or Excel.
Frequently Asked Questions
Why does my LARGE(FILTER(...)) formula return #CALC! in WPS Spreadsheets?
This usually means FILTER found no columns matching the selected date. Check that the date cell and the header dates are real dates, not text, and that the selected date exists in the date header range.
Can I use this LARGE and FILTER formula in WPS Office for a .xlsx file?
Yes. WPS Spreadsheets supports Excel .xlsx workbooks and many modern lookup functions, including FILTER and XLOOKUP in current versions. Open your .xlsx workbook and enter the formula as you would in Excel.
How do I return the item description for the second largest value for a matching date?
First use LARGE(FILTER(...),2) to get the second largest value. Then use XLOOKUP with two conditions: one that matches the date header and one that matches the ranked value, for example =XLOOKUP(1,(date_range=selected_date)*(value_range=ranked_value),description_range,"Not found").
What if my WPS Spreadsheets version does not have FILTER?
Use the array formula =LARGE(IF(date_header_range=selected_date,value_range),1) and press Ctrl+Shift+Enter. If you still get #NAME?, update WPS Office to a version that includes modern dynamic array functions.
Does the date row have to be in a specific position for this formula to work?
The formula expects one date header row and one or more value rows. You can adjust the ranges to match your layout; just keep the date header range and value range the same number of columns.
