'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the First Positive Value in a Row in Excel
Question details
The user needs an Excel formula to identify the first cell in a row that contains a value greater than zero, and then retrieve the corresponding column header (such as a date) from the top row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing row-by-row data to determine the exact date or period when a value first becomes positive.
- Observed behavior
- Searching left to right across a specific row to return the exact header date of the first occurrence of a value >0 without returning approximate matches.
Ensure your data range is contiguous and your header row is locked using absolute references (like $D$3:$H$3) so the formula can be copied down multiple rows without errors.
Use the INDEX and MATCH Array Formula
This method reliably evaluates each cell in a row to check if it is greater than zero, returning the first matching column header from left to right.
By combining INDEX and MATCH with an array logic, Excel can evaluate a condition (greater than zero) across multiple columns simultaneously.
Click on the cell where you want the corresponding date result to appear (for example, C4).
Type the formula: =INDEX($D$3:$H$3, MATCH(TRUE, $D4:$H4>0, 0)) into the formula bar.
Press Ctrl+Shift+Enter to evaluate it as an array formula (newer Excel versions may only require pressing Enter). Curly braces {} will automatically appear around the formula.
Click and drag the fill handle at the bottom right of the cell down to apply the formula to the remaining rows in your dataset.
Use the XLOOKUP Function (Newer Versions Only)
A modern and simpler approach for users on Excel 2021, Microsoft 365, or newer versions of WPS Office, avoiding the need for traditional array entry.
Use WPS Spreadsheet to Analyze Row Data
WPS Spreadsheet fully supports advanced array formulas like INDEX, MATCH, and XLOOKUP, making it incredibly easy to find the first positive values and extract corresponding headers from your datasets.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook (.xlsx).
- 2. Select your target cell: Click on the cell where you want to output the corresponding header date.
- 3. Input the array formula: Type =INDEX($D$3:$H$3, MATCH(TRUE, $D4:$H4>0, 0)) into the formula bar.
- 4. Calculate the result: Press Ctrl+Shift+Enter to complete the array calculation, then drag the cell downwards to fill the rest of your data.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
This error occurs if no positive value (greater than zero) is found in the specified row range. You can wrap your formula in an IFERROR function to handle this gracefully. For example: =IFERROR(INDEX($D$3:$H$3, MATCH(TRUE, $D4:$H4>0, 0)), "No positive value").
Can I find the last positive value in a row instead of the first?
Yes. If you are using Microsoft 365, Excel 2021, or recent versions of WPS Office, you can use the XLOOKUP function with the search mode set to -1 (search last to first). The formula would be: =XLOOKUP(TRUE, D4:H4>0, D3:H3, "Not Found", 0, -1).
Do I always need to press Ctrl+Shift+Enter for the INDEX and MATCH formula?
In older versions of Excel (2019 and earlier) and older versions of spreadsheet software, Ctrl+Shift+Enter is required because it forces the program to evaluate the logic as an array formula. In newer versions that support dynamic arrays, simply pressing Enter is sufficient.
