How to Return the Column Header for the First Positive Value in a Row in WPS Office Spreadsheets
Question details
The user needs a formula that scans each row from left to right, finds the first value greater than zero, and returns that column's header in another column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has rows of numeric values with column headers such as years and wants a result column that shows the header of the first value greater than zero in each row.
- Observed behavior
- The user needs a worksheet formula to identify the first positive cell and return its corresponding header, such as returning 2028 when the first positive value is in column G.
Make sure your data has a single header row, for example B1:L1, and that the values to test are in a contiguous range on each data row, such as B2:L2.
Use XLOOKUP to Return the Header of the First Positive Value
Best for modern WPS Spreadsheets and Microsoft 365/Excel 2021 builds because XLOOKUP keeps the formula short and readable.
XLOOKUP evaluates B2:L2>0 as an array of TRUE/FALSE values, finds the first TRUE, and returns the corresponding header from B$1:L$1.
Open WPS Office Spreadsheets and select cell N2, the first cell in your results column.
Type =XLOOKUP(TRUE,B2:L2>0,B$1:L$1,"No match") and press Enter.
Drag the fill handle from N2 down through the remaining rows in your table.
Use INDEX, SMALL, and IF for Excel 2013 and Older WPS Builds
Use this classic array formula when XLOOKUP is not available or when you need compatibility with Excel 2013-style array formulas.
Use FILTER and INDEX in Dynamic Array Versions
Alternative for dynamic array-enabled WPS Spreadsheets or Excel versions; it is useful when you already use FILTER elsewhere.
Return the First Positive Value’s Header in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the formula shown above. WPS Spreadsheets handles the same lookup formulas and Excel .xlsx files, so you can return the correct header for the first positive value in each row without changing tools.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx workbook that contains the header row and data rows.
- 2. Select the result cell: Click N2, or the first cell where you want each row's returned header.
- 3. Type the formula: Enter =XLOOKUP(TRUE,B2:L2>0,B$1:L$1,"No match") or the classic INDEX/SMALL/IF array formula described above.
- 4. Copy it down: Drag the fill handle from N2 through the remaining data rows to populate results.
- 5. Review the returned headers: Confirm that rows with a first positive value in column G return 2028 or the matching header.
Frequently Asked Questions
Which formula should I use in WPS Office if XLOOKUP is not available?
Use the classic array formula: =INDEX($B$1:$L$1,SMALL(IF($B2:$L2>0,COLUMN($B$1:$L$1)-1),1)). If your WPS build does not treat it as a dynamic array formula, enter it with Ctrl+Shift+Enter.
Can I return the header for the first negative value instead?
Yes. Replace the >0 test with <0. For example: =XLOOKUP(TRUE,B2:L2<0,B$1:L$1,"No match"), or use IF($B2:$L2<0,COLUMN($B$1:$L$1)-1) in the INDEX/SMALL/IF formula.
Why does the INDEX/SMALL/IF formula show #NUM! when no positive values exist?
The formula cannot find a first positive position, so it returns #NUM!. Wrap it with IFERROR to display a custom message: =IFERROR(INDEX($B$1:$L$1,SMALL(IF($B2:$L2>0,COLUMN($B$1:$L$1)-1),1)),"No match").
Does this formula work if my headers are in a column instead of a row?
No, these formulas are designed for a horizontal header row. If your headers are in a column and values are in a row, the formula needs to be transposed using INDEX/MATCH or related functions.
