WPS Spreadsheet Formula to Return a Revision by Part Number and Date Range
Question details
The user needs a formula that returns the correct revision by matching both a part number and an applicable date range.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A revision table contains part numbers, revision values, start dates, and end dates. The part number may be listed once followed by blank cells, and date ranges for different parts may overlap.
- Observed behavior
- A date-only lookup can return a revision for the wrong part number when date ranges overlap.
Confirm which cells contain the requested part number and date, such as B2 and B3, and identify the revision, start-date, end-date, and part-number columns. If part numbers are shown only on the first row of each group, prepare a helper column to fill the value down before using the lookup formula.
Use a helper column and a three-condition revision lookup
Use this compatible approach when your WPS Spreadsheet version does not support dynamic-array functions such as LET, SCAN, or FILTER.
This method fills each blank part-number cell with the part number from the row above, then checks the part number, start date, and end date together. It prevents overlapping date ranges from returning a revision for another part.
In the helper column, enter =IF(A8<>"",A8,F7) in F8, replacing A8 with the source part-number cell and F7 with the previous helper cell. Fill the formula down through the data range.
Enter the target part number in B2 and the lookup date in B3. Make sure B3 is stored as a valid date using Home > Number Format > Date if necessary.
Select the result cell and enter =INDEX($C$8:$C$21,MATCH(1,($D$8:$D$21<=$B$3)*($E$8:$E$21>=$B$3)*($F$8:$F$21=$B$2),0)). In older WPS Spreadsheet versions, confirm the formula with Ctrl+Shift+Enter.
Change B2 or B3 and verify that the returned value in column C changes only when both the part number and date conditions are satisfied.
Use one dynamic formula for Microsoft 365 or newer spreadsheet versions
Use this compact formula when your spreadsheet supports LET, SCAN, FILTER, and CHOOSECOLS functions.
Build reliable part-number and date-range revision lookups in WPS Spreadsheet
WPS Spreadsheet lets you organize revision tables, fill grouped part numbers with a helper formula, and use INDEX and MATCH to return the revision that satisfies the part-number, start-date, and end-date conditions.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the revision table.
- 2. Fill down grouped part numbers: Add a helper column and use =IF(A8<>"",A8,F7), then drag the fill handle down the table.
- 3. Enter lookup inputs: Type the requested part number in B2 and the applicable date in B3.
- 4. Add the lookup formula: In the result cell, use the INDEX/MATCH formula with the start-date, end-date, and helper-column conditions.
Frequently Asked Questions
How do I stop a date-range lookup from returning another part's revision?
Include the part-number condition in the same MATCH expression as the start-date and end-date conditions. The helper-column formula =IF(A8<>"",A8,F7) fills blank part-number rows so every record can be checked.
What formula returns a revision when the date is between the start and end dates?
Use =INDEX($C$8:$C$21,MATCH(1,($D$8:$D$21<=$B$3)*($E$8:$E$21>=$B$3)*($F$8:$F$21=$B$2),0)), adjusting the ranges to match your table.
Why does my INDEX and MATCH formula need Ctrl+Shift+Enter?
Older spreadsheet versions evaluate the multiplied conditions as an array. After entering the formula in WPS Spreadsheet, press Ctrl+Shift+Enter if a normal Enter does not calculate it correctly.
Can I do this without a helper column?
Yes, in Microsoft 365 or another version supporting dynamic arrays, use LET with SCAN to fill down the part number and FILTER to apply the part-number and date conditions in one formula.
