Find the First Earlier Excel Value Above a Threshold and Return Its Date
Question details
Find the first nonblank value in earlier rows that is greater than or equal to a target amount, and return that value or its related date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains dates in column A and amounts in column B. A formula on a later row needs to search prior rows for the first amount that meets a specified threshold.
- Observed behavior
- The desired result is the first qualifying earlier amount and the date from the corresponding row.
Confirm that the date and amount columns begin on the same row and that the amount cells contain numeric values. Replace the example threshold, current-row reference, and search range with the references used in your worksheet.
Use INDEX and MATCH to return the first earlier amount meeting the threshold
Use this formula when you need the first nonblank amount in a defined earlier range that is greater than or equal to a target value.
The MATCH portion finds the first row where the amount is at least 12.5, is not blank, and occurs before row 1001. INDEX then returns the amount from that matched row.
In the result cell, enter =IFERROR(INDEX(B:B,MATCH(1,(B1:B1000>=12.5)*(B1:B1000<>"")*(ROW(B1:B1000)<ROW(B1001)),0)),""). Replace 12.5 with your target amount and change B1001 to the row containing the formula or current record.
In older Excel versions, confirm the formula with Ctrl+Shift+Enter so it is evaluated as an array formula. In Microsoft 365 and current versions that support dynamic arrays, press Enter.
Use the fill handle to copy the formula down. Review the relative references so each row searches only the rows above its own position.
Return the corresponding date with the same MATCH conditions
Use this variation when the qualifying amount is in column B but you need the date stored in column A.
Find Earlier Threshold Values and Dates in WPS Spreadsheets
WPS Spreadsheets lets you use INDEX, MATCH, ROW, and IFERROR to identify the first earlier amount that reaches a threshold and return the matching value or date from the same row.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the date and amount columns.
- 2. Add the earlier-value formula: Select the result cell and enter =IFERROR(INDEX(B:B,MATCH(1,(B1:B1000>=12.5)*(B1:B1000<>"")*(ROW(B1:B1000)<ROW(B1001)),0)),"").
- 3. Add the related-date formula: Select a date result cell and use the same MATCH expression with INDEX(A:A,...) to return the date from column A.
- 4. Apply the formula to other records: Drag the fill handle down the results column, then adjust the threshold and search-range references where needed.
Frequently Asked Questions
How do I return the first value above a threshold rather than the last one?
Use MATCH with a lookup value of 1 and conditions multiplied together. MATCH returns the first position where all conditions evaluate to TRUE, as in MATCH(1,(range>=threshold)*(range<>""),0).
Why does the formula include ROW(B1:B1000)<ROW(B1001)?
This condition limits the lookup to rows above the current row. Replace B1001 with the cell on the current row so the formula does not match the current or later records.
How can I return a date associated with the matching amount?
Use the identical MATCH expression and change the INDEX return column to the date column. For dates in column A and amounts in column B, use INDEX(A:A,MATCH(...)).
What happens if no earlier amount reaches the target?
The IFERROR wrapper returns an empty string, which displays as a blank cell. Remove IFERROR if you prefer to show the #N/A error instead.
Do I need Ctrl+Shift+Enter for this formula?
Older Excel releases require Ctrl+Shift+Enter for this array formula. Microsoft 365 and other versions with dynamic-array calculation generally accept it with Enter.
