WPS Spreadsheet: Find the Minimum Temperature by Date with INDEX and MATCH
Question details
Find the lowest temperature recorded across three stations for a selected date and optionally return the station with that minimum temperature.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Temperature data is arranged by date in rows and station names in columns. A selected date must be matched to its row, including cases where some dates are missing.
- Observed behavior
- The required result is the minimum temperature for the matched date, with an optional station name identifying where that minimum occurred. If the date is absent, the formula should return a blank instead of an error.
Confirm that the date column, station temperature range, and station header row are aligned. Place the date to search in H4 and verify that the lookup dates use the same date format as the source data.
Use INDEX, MATCH, and MIN to return the minimum temperature
Use this formula when you need the lowest temperature across all station columns for the date selected in H4.
MATCH locates the selected date in the date column, while INDEX returns the corresponding row of station temperatures. MIN then evaluates that row and returns the lowest value.
Wrap the formula with IFERROR when a missing date should produce a blank result.
In WPS Spreadsheet, click the cell where you want the minimum temperature to appear.
Enter =IFERROR(MIN(INDEX($C$4:$E$9,MATCH(H4,$B$4:$B$9,0),)),"") and press Enter. The formula matches the date in H4 and evaluates the temperatures in the corresponding row.
Change the date in H4 to confirm that the result updates. If H4 does not exist in $B$4:$B$9, the IFERROR wrapper returns a blank.
Analyze date-based temperature data in WPS Spreadsheet
WPS Spreadsheet lets you combine INDEX, MATCH, and MIN to retrieve a minimum value from the row matched to a selected date, then identify the station associated with that result. The same workbook can handle missing-date checks with IFERROR.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the date and station temperature data.
- 2. Set the lookup date: Enter or select the date to search in H4, ensuring it matches the source date format.
- 3. Calculate the minimum: In an empty result cell, enter =IFERROR(MIN(INDEX($C$4:$E$9,MATCH(H4,$B$4:$B$9,0),)),"") and press Enter.
- 4. Return the station: In another result cell, enter the station lookup formula using INDEX and MATCH against the minimum row value.
Frequently Asked Questions
What formula returns the minimum temperature for the date in H4?
Use =MIN(INDEX($C$4:$E$9,MATCH(H4,$B$4:$B$9,0),)). To show a blank when the date is missing, use =IFERROR(MIN(INDEX($C$4:$E$9,MATCH(H4,$B$4:$B$9,0),)),"").
How can I return the station with the minimum temperature?
Use =INDEX($C$3:$E$3,MATCH(MIN(INDEX($C$4:$E$9,MATCH(H4,$B$4:$B$9,0),)),INDEX($C$4:$E$9,MATCH(H4,$B$4:$B$9,0),),0)). This matches the minimum value to the station header row.
What happens if the selected date is not in the data?
Without error handling, MATCH returns an error because it cannot find the date. Wrap the complete formula in IFERROR, such as =IFERROR(formula,""), to display a blank.
Which ranges should contain the dates, temperatures, and station names?
In the example, dates are in B4:B9, station temperatures are in C4:E9, and station names are in C3:E3. Adjust these absolute references to match your worksheet layout.
Can this formula handle missing temperature values for one station?
Yes, MIN evaluates the available numeric values in the matched row. Ensure missing values are blank or nonnumeric; if an error value is present in the row, address that error before calculating the minimum.
