Find the Closest Percentage and Most Recent Date in WPS Spreadsheets
Question details
Find the percentage in M4:M100000 that is closest to the reference value in M1, and if multiple percentages are equally close, select the one associated with the most recent date in A4:A100000.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a large dataset with percentages and associated dates and needs a formula-based lookup that respects both proximity and recency.
- Observed behavior
- The formula should return the percentage that is closest to M1; when multiple percentages tie for closeness, it should return the value with the latest date in A4:A100000.
Open the workbook in WPS Spreadsheets and confirm that the target value is in M1, percentages are in M4:M100000, and dates in A4:A100000 are stored as real date values. Add a blank helper column, for example column N, to hold the absolute difference formulas.
Find the closest percentage and break ties by the most recent date with a helper column and INDEX/MATCH array formulas
Use this Excel-compatible formula method in WPS Spreadsheets when you need to compare a large range, identify the minimum absolute difference, and return the matching value tied to the latest date.
The helper column makes the comparison transparent by storing how far each percentage is from the reference value. The MIN function then finds the smallest difference, and the MAX IF array formula returns the most recent date among the tied closest values.
Select cell N4, enter =ABS(M4-$M$1), press Enter, and then fill the formula down through N100000 or the last row of data.
Select an empty cell and enter the array formula =MAX(IF(N4:N100000=MIN(N4:N100000),A4:A100000)). Confirm with Ctrl+Shift+Enter to return the most recent date where the absolute difference equals the minimum.
In the result cell, enter =INDEX(M4:M100000,MATCH(1,(N4:N100000=MIN(N4:N100000))*(A4:A100000=MAX(IF(N4:N100000=MIN(N4:N100000),A4:A100000))),0)). Confirm with Ctrl+Shift+Enter to return the percentage associated with both the closest difference and the most recent date.
Complete the closest percentage and latest date lookup in WPS Spreadsheets
Open the workbook in WPS Spreadsheets and build the same ABS, MAX IF, and INDEX/MATCH array formula chain. WPS Spreadsheets supports Excel-compatible formulas, so you can perform the closest-percentage lookup and date-based tie-break directly in your existing workbook.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook that contains the percentages and matching dates.
- 2. Create the helper column: Select N4, type =ABS(M4-$M$1), press Enter, and fill down to N100000.
- 3. Enter the latest-date tie-break formula: Select the latest-date result cell, type =MAX(IF(N4:N100000=MIN(N4:N100000),A4:A100000)), and press Ctrl+Shift+Enter.
- 4. Enter the INDEX/MATCH result formula: Select the percentage result cell, type =INDEX(M4:M100000,MATCH(1,(N4:N100000=MIN(N4:N100000))*(A4:A100000=MAX(IF(N4:N100000=MIN(N4:N100000),A4:A100000))),0)), and press Ctrl+Shift+Enter.
- 5. Verify the returned value: Check that the returned percentage is the closest to M1 and that it belongs to the most recent date in A4:A100000.
Frequently Asked Questions
Why do I need a helper column for this lookup?
The helper column stores the absolute difference between each percentage and the target value in M1. This makes it easier to find the minimum difference with MIN and to compare all rows against that minimum without repeating calculations.
Do I have to press Ctrl+Shift+Enter in WPS Spreadsheets?
In WPS Spreadsheets, legacy array formulas such as MAX IF and this INDEX/MATCH MATCH condition should be confirmed with Ctrl+Shift+Enter for compatibility. If your WPS version supports dynamic arrays, pressing Enter may work, but Ctrl+Shift+Enter is the safest approach.
What should I do if the dates in A4:A100000 are stored as text?
The MAX IF formula may return 0 or an incorrect result. Convert the text entries to real date values first using Text to Columns, DATEVALUE, or by correcting the cell format and re-entering the dates.
Can I adapt the formula to a smaller range instead of row 100000?
Yes. Replace N4:N100000, A4:A100000, and M4:M100000 with the exact rows that contain your data. Avoid selecting entire columns in legacy array formulas because it can slow down calculation and may evaluate empty cells incorrectly.
What does the formula return if no closest percentage is found?
If the referenced ranges are empty or contain no valid numbers or dates, the MATCH part cannot find a match and the formula returns #N/A. Make sure M4:M100000 contains percentages and A4:A100000 contains valid dates.
