Return the Most Recent Text Value Above a Number in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that returns a blank for text rows and, for numeric rows, returns the most recent text value appearing above the current row instead of always returning row 1.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a column where text names appear above numeric rows and wants each numeric row to display the latest name listed above it.
- Observed behavior
- An absolute reference to A1 always returns the first text value, so it does not update to the most recent text above each number.
Verify that column A contains the mixed text names and numbers, and that the result column such as F is empty. The formula must start in F2 and use an expanding range from $A$2 to the current row, so data must begin at row 2.
Use an Expanding INDEX and MATCH Formula to Return the Latest Text Above a Number
Use this Excel-compatible formula in WPS Spreadsheets when text rows should remain blank and numeric rows must show the most recent text value above them.
The formula checks whether the current value in column A is numeric. If it is not a number, the formula returns an empty string. If the current value is numeric, MATCH("ZZZ",$A$2:A2) locates the last text entry in the expanding range, and INDEX returns that text value.
Click cell F2, or the first cell in the column where you want the result to appear.
Type =IF(NOT(ISNUMBER(A2)),"",INDEX($A$2:A2,,MATCH("ZZZ",$A$2:A2))) and press Enter.
Drag the fill handle from F2 down through all rows that contain data in column A.
Return the Latest Text Above a Number with WPS Spreadsheets
WPS Spreadsheets supports the same INDEX, MATCH, IF, and ISNUMBER functions as Microsoft Excel, so you can open your workbook, enter the formula, and copy it down without changing tools. Save the file as .xlsx or use WPS formats with full compatibility.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and use File > Open to open the Excel workbook containing your column A data.
- 2. Click the first result cell: Select F2 or the first cell in the result column where you want the returned text to appear.
- 3. Enter the formula: Type =IF(NOT(ISNUMBER(A2)),"",INDEX($A$2:A2,,MATCH("ZZZ",$A$2:A2))) and press Enter.
- 4. Copy the formula down: Drag the fill handle from F2 downward through all rows that have data in column A.
- 5. Check the results: Confirm that nonnumeric rows are blank and each numeric row shows the most recent text value above it.
Frequently Asked Questions
Why does using an absolute reference to A1 always return the first value?
A reference such as $A$1 always points to the first row, so the lookup never expands as you copy the formula down. Replace it with an expanding range like $A$2:A2 to consider only the current row and the rows above.
What does MATCH("ZZZ",$A$2:A2) do in this formula?
It searches the expanding range for "ZZZ" by using an approximate text lookup, which is a common way to identify the last text entry in a range. INDEX then returns that found text value.
Can I use this formula if my data starts in a row other than 2?
Yes. Change both $A$2 references and the starting cell references to match your first data row. For example, if data starts at A5, use INDEX($A$5:A5,,MATCH("ZZZ",$A$5:A5)) and adjust the row checks accordingly.
Will this formula work in both WPS Office and Microsoft Excel?
Yes. IF, NOT, ISNUMBER, INDEX, and MATCH are supported in WPS Spreadsheets and Microsoft Excel, so the same formula can be used in .xlsx workbooks.
What if my text names contain symbols or values that start with Z?
The "ZZZ" lookup works for most ordinary text names. If your data includes text that may sort after "ZZZ", use a larger lookup string such as REPT("z",255) inside MATCH to cover longer text values.
