WPS Spreadsheets INDEX MATCH Formula to Return Time Values
Question details
The user needs to return a time value from a table when date, number, status, and location criteria all match.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A table contains dates, numbers, statuses, locations, and time values. Criteria are entered in separate cells and should identify the matching time.
- Observed behavior
- The goal is to return the matching time value and display a blank when no combination is found.
Confirm that the source columns use consistent date, number, status, and location formats, and verify that the result range contains valid time values. Check the criteria cells H5, I5, J5, and K4 before entering the formula.
Use INDEX and MATCH for a four-criteria time lookup
Use this solution when one row should be returned for the matching date, number, status, and location combination.
The formula creates four TRUE/FALSE tests, multiplies them so only a row meeting every criterion produces 1, and uses INDEX to return the corresponding time from column E. IFERROR displays a blank when no match exists.
In WPS Spreadsheets, click the cell where the matching time should appear, such as the cell at the intersection of the criteria row and date header.
Enter =IFERROR(INDEX($E$3:$E$18,MATCH(1,($B$3:$B$18=$H5)*($D$3:$D$18=$I5)*($C$3:$C$18=$J5)*($A$3:$A$18=K$4),0)),"") and press Enter.
If your WPS Spreadsheets version does not calculate the array automatically, press Ctrl+Shift+Enter instead of Enter to confirm the formula.
Drag the fill handle across or down the result area. The mixed references keep the source ranges fixed while the criteria references adjust for each row or column.
Return matching time values with WPS Spreadsheets
WPS Spreadsheets lets you build the same multi-criteria INDEX and MATCH lookup, fill it across a table, and keep the result blank when no matching record exists.
- 1. Open your workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the source table.
- 2. Prepare the criteria cells: Verify the date, number, status, and location criteria in the cells referenced by the formula.
- 3. Enter the lookup formula: Select the output cell and enter the INDEX and MATCH formula with the source ranges and criteria references.
- 4. Confirm and fill the formula: Press Enter, or Ctrl+Shift+Enter when array confirmation is required, then drag the fill handle through the output range.
Frequently Asked Questions
Which cells does the formula use for the lookup criteria?
The formula compares column B with H5, column D with I5, column C with J5, and column A with K4. Adjust these references if your criteria are stored elsewhere.
Why does the formula return a blank instead of an error?
IFERROR surrounds the INDEX and MATCH calculation. When no row satisfies all four conditions, IFERROR returns an empty string instead of an error message.
Do I need to press Ctrl+Shift+Enter in WPS Spreadsheets?
Use Ctrl+Shift+Enter if your WPS Spreadsheets version requires traditional array-formula confirmation. Newer versions may calculate the multiplied criteria array after you press Enter.
What happens if two rows meet all four criteria?
MATCH with 0 returns the first matching row, so INDEX returns the time from that row. Remove duplicates or use a PivotTable when each criteria combination should have only one time value.
