WPS Spreadsheets: Use INDEX and MATCH for Date and Article Price Lookups
Question details
The user needs to return an article price based on both an article name and a delivery date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A pricing table contains different article prices for date ranges, and the user wants a maintainable lookup instead of nested IF and VLOOKUP formulas.
- Observed behavior
- The goal is to identify the price column by article and the matching row whose start and end dates contain the delivery date.
Arrange the article names as headers above the price columns, and place the start date and end date for each pricing period in separate columns. Confirm that the delivery date and date-range cells are stored as valid spreadsheet dates rather than text.
Combine INDEX and MATCH for a two-condition price lookup
Use this method when the price depends on both the article header and a delivery date within a defined start-and-end range.
In this example, article names are in B1:D1, prices are in B2:D100, start dates are in E2:E100, end dates are in F2:F100, the delivery date is in G2, and the article name is in H2. The first MATCH selects the row whose date range contains the delivery date; the second MATCH selects the article column.
In WPS Spreadsheets, place article names in B1:D1, corresponding prices in B2:D100, start dates in E2:E100, and end dates in F2:F100. Enter the delivery date in G2 and the article name in H2.
Select the result cell and enter =INDEX($B$2:$D$100,MATCH(1,INDEX(($E$2:$E$100<=G2)*($F$2:$F$100>=G2),0),0),MATCH(H2,$B$1:$D$1,0)). Press Enter to return the price.
Change the delivery date in G2 or the article name in H2. WPS Spreadsheets will recalculate the result using the matching article column and the row whose start and end dates contain the date.
Add IFERROR when a date range or article is missing
Use this variation when the lookup table may not contain a matching date period or article and you want a clear message instead of an error.
Build flexible date-and-article price lookups in WPS Spreadsheets
WPS Spreadsheets lets you organize pricing tables and use INDEX with MATCH to return values by article and date range. Its familiar formula workflow supports Excel-compatible workbooks, making it easy to replace nested IF and VLOOKUP formulas.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the pricing workbook from the Home screen.
- 2. Organize the lookup ranges: Place article headers above the price columns and keep start dates and end dates in separate columns beside the pricing table.
- 3. Enter the lookup inputs: Type the delivery date and article name into dedicated cells, such as G2 and H2.
- 4. Insert the formula: Select the result cell and use =INDEX($B$2:$D$100,MATCH(1,INDEX(($E$2:$E$100<=G2)*($F$2:$F$100>=G2),0),0),MATCH(H2,$B$1:$D$1,0)) to return the matching price.
Frequently Asked Questions
What does the first MATCH function do in this lookup?
It finds the row where the delivery date is greater than or equal to the start date and less than or equal to the end date. The expression ($E$2:$E$100<=G2)*($F$2:$F$100>=G2) produces a 1 for the matching row.
What does the second MATCH function do?
It finds the position of the article name in H2 within the article headers in B1:D1. INDEX then uses that position to select the correct price column.
Why does the formula return an error?
Check that the delivery date is a valid date, the start and end ranges are aligned with the price rows, and the article name exactly matches a header. Also make sure the date ranges do not overlap unexpectedly.
Can I use this formula with more than three articles?
Yes. Expand the price range and header range, such as B2:Z100 and B1:Z1, while keeping the article headers aligned with their price columns.
How can I show a message when no price is available?
Wrap the INDEX and MATCH formula in IFERROR, for example =IFERROR(your_formula,"No matching price"). This displays a readable message when the article or date range cannot be found.
