WPS Spreadsheet: Find the Last Text or Number in a Row
Question details
Return the value in one row that is adjacent to the last text or numeric value of a specified type in another row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains a source row with text or numbers and a related row whose value should be returned from the same column as the last matching entry.
- Observed behavior
- The user needs a formula that identifies the last text or numeric value in a row and returns the corresponding value from another row.
Confirm which row contains the values to search and which row contains the related results. Replace the example ranges A3:O3 and A4:O4 with matching ranges of the same width in your worksheet.
Use INDEX and LOOKUP to return the value beside the last text or number
Use this formula method when you need the last matching text or numeric entry in a row and the corresponding value from another row.
The formula searches the specified row from left to right, identifies the final cell matching ISTEXT or ISNUMBER, and uses INDEX to return the value from the related row in the same column.
In WPS Spreadsheet, click the cell where you want the related value to appear.
Enter =INDEX(A3:O3,1,LOOKUP(2,1/(ISTEXT(A4:O4)),COLUMN(A4:O4)-COLUMN(A4)+1)) and press Enter. This returns the value from row 3 beside the last text entry in row 4.
To search for the last number instead, enter =INDEX(A3:O3,1,LOOKUP(2,1/(ISNUMBER(A4:O4)),COLUMN(A4:O4)-COLUMN(A4)+1)) and press Enter.
If your WPS Spreadsheet version does not calculate the array expression after pressing Enter, press Ctrl+Shift+Enter to confirm the formula.
Find the Last Matching Entry with WPS Spreadsheet Formulas
WPS Spreadsheet can identify the last text or numeric value in a row and return the corresponding value from another row using INDEX, LOOKUP, ISTEXT, or ISNUMBER. Its familiar spreadsheet interface makes it easy to enter, edit, and reuse these formulas.
- 1. Open the worksheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the two related rows.
- 2. Choose the output cell: Click the cell where the value adjacent to the last matching entry should be displayed.
- 3. Enter the text formula: Type =INDEX(A3:O3,1,LOOKUP(2,1/(ISTEXT(A4:O4)),COLUMN(A4:O4)-COLUMN(A4)+1)) to return the value beside the last text entry.
- 4. Switch to numeric matching when needed: Replace ISTEXT with ISNUMBER and press Enter to return the value beside the last numeric entry.
Frequently Asked Questions
How do I find the last text value in a row in WPS Spreadsheet?
Use =INDEX(A3:O3,1,LOOKUP(2,1/(ISTEXT(A4:O4)),COLUMN(A4:O4)-COLUMN(A4)+1)). Adjust the two ranges to match your return row and search row.
What formula finds the last number in an Excel or WPS Spreadsheet row?
Use the same INDEX and LOOKUP structure, replacing ISTEXT with ISNUMBER: =INDEX(A3:O3,1,LOOKUP(2,1/(ISNUMBER(A4:O4)),COLUMN(A4:O4)-COLUMN(A4)+1)).
Why does the formula return an error when no matching value exists?
LOOKUP cannot find a position when the search row contains no text or no numbers of the requested type. Check the search range or wrap the formula in IFERROR to display a custom message.
Do older WPS Spreadsheet versions require an array-key combination?
Some older versions may require you to confirm the formula with Ctrl+Shift+Enter instead of Enter. If the result is not calculated correctly, select the formula cell, press F2, and then press Ctrl+Shift+Enter.
Can I use different rows or columns than A3:O3 and A4:O4?
Yes. Replace the example ranges with your own return and search ranges, keeping both ranges the same width and aligned to the same columns.
