Return the Last Matching Value from Selected Excel Cells in WPS Office
Question details
The user needs a spreadsheet formula that returns the last numeric value in a row while filtering by a specified column header or selected range.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a table with header cells and row data, and they need to extract the rightmost numeric entry that belongs to a chosen column header.
- Observed behavior
- A normal lookup may return a wrong or text value because it does not combine numeric filtering with header matching across the row.
Confirm that the header row and data row references are consistent, and make sure the lookup header cell exactly matches the header text in the source range.
Use a LOOKUP formula to return the last matching numeric value
Use this formula when you need a compatible, non-dynamic array solution that works in WPS Office Spreadsheets and standard Excel versions.
The LOOKUP formula filters the row by two conditions: the source cell must be numeric and its header must match the selected header cell. It then returns the last matching value from the range.
Click the cell where the result should appear, for example H4.
Type =LOOKUP(2,1/(ISNUMBER($L4:$W4)*($L$3:$W$3=H$3)),$L4:$W4) in the formula bar.
Change $L4:$W4 to your data row and $L$3:$W$3 to your header row, keeping the mixed references so the formula can be copied if needed.
Press Enter to confirm the formula. The cell now shows the last numeric value whose header matches H3.
Use TOROW and TAKE for a dynamic array alternative
Choose this method when your spreadsheet supports dynamic array functions and you want to return the rightmost values from a filtered row.
Return the last matching value in WPS Office Spreadsheets
Open your Excel workbook in WPS Office Spreadsheets and use a standard LOOKUP formula to return the rightmost numeric value under a matching header. WPS Office keeps your .xlsx files compatible while providing free, familiar spreadsheet tools.
- 1. Open the workbook: Open the target Excel workbook in WPS Office Spreadsheets.
- 2. Select the result cell: Click the cell where you want to return the last matching value.
- 3. Enter the formula: Type =LOOKUP(2,1/(ISNUMBER($L4:$W4)*($L$3:$W$3=H$3)),$L4:$W4) and adjust the ranges to your data.
- 4. Confirm and verify: Press Enter and verify that the returned value is the last numeric entry under the matching header.
- 5. Save the workbook: Save the file as .xlsx to maintain compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the formula use LOOKUP with a division expression?
The expression 1/(ISNUMBER($L4:$W4)*($L$3:$W$3=H$3)) creates an array of 1 or #DIV/0! values. LOOKUP searches for 2, skips errors, and returns the last position where the condition is true.
Can this formula return text instead of numeric values?
The numeric formula is designed for numbers. To return the last text value under a matching header, use a formula such as =LOOKUP(2,1/($L$3:$W$3=H$3),$L4:$W4) and confirm it fits your data layout.
What should I change if my data is in a different row?
Change $L4:$W4 to the row containing your values, for example $L10:$W10, and change $L$3:$W$3 to the actual header row range.
Does WPS Office support TOROW and TAKE?
Support depends on the WPS Office version and update channel. If those functions are not available, use the LOOKUP method because it works in standard spreadsheet versions.
Why does my formula return #N/A?
#N/A usually appears when no cell in the row is both numeric and under a header that exactly matches the lookup header cell. Check for extra spaces, case mismatches, or nonnumeric values.
