How to Return the Last Nonblank Value from an Excel Table Column
Question details
The user needs to retrieve the last non-empty value from a specific column within an Excel table located on a different worksheet.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Using an XLOOKUP formula to dynamically pull the last running total from a formatted data table onto a separate reporting worksheet.
- Observed behavior
- The formula successfully calculates on the source sheet but returns a range-reference error when entered on another worksheet.
Ensure your dataset is formatted as an official Table (Insert > Table) and verify the exact spelling of both your Table Name (e.g., TblData) and Column Header to ensure structured references work across sheets.
Use XLOOKUP with Structured References (Recommended)
This method works universally for both text and numeric values by forcing XLOOKUP to search the table column from bottom to top, bypassing sheet-specific reference errors.
Structured references allow you to call a table and its columns by name from anywhere in the workbook, preventing the typical #REF! errors associated with cross-sheet formulas.
Navigate to the worksheet where you want to display the final result and select an empty cell.
Type the formula: =XLOOKUP(TRUE,TblData[Running Total]<>"",TblData[Running Total],"",0,-1)
Press Enter. Ensure you replace 'TblData' and 'Running Total' with your actual table and column names.
Use LOOKUP Formula for Numeric Data
If the target column contains strictly numeric values, a classic LOOKUP formula is highly efficient and straightforward.
Use INDEX and COUNT Combination
This approach is ideal for continuous numeric columns that do not have intermediate blank cells.
Easily Manage Table References and Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet offers powerful, built-in support for advanced functions like XLOOKUP, LOOKUP, and structured table referencing. Effortlessly manage large datasets and pull cross-sheet data without frustrating reference errors.
- 1. Open your Dataset: Launch WPS Spreadsheet and open your existing workbook.
- 2. Format as Table: Select your data range and press Ctrl+T to convert it into a designated Table, enabling global workbook references.
- 3. Insert XLOOKUP: Use WPS's intelligent formula builder to seamlessly insert your XLOOKUP function on any sheet.
Frequently Asked Questions
Why does my formula return a #REF! error when copied to another sheet?
A #REF! error usually happens if you are using standard cell references (like A2:A100) instead of global structured references, and the sheet name isn't properly included. By formatting your data as a Table and using TableName[ColumnName], the formula dynamically recognizes the data from any worksheet in the file.
Can I use the LOOKUP(9^99) method if my column contains text?
No, the =LOOKUP(9^99, range) method only works for finding the last numeric value. If your column contains text, or a mix of text and numbers, you should use the XLOOKUP method instead.
What does the <>"" part of the XLOOKUP formula do?
The <>"" is a logical operator in Excel that means "is not equal to blank". In the XLOOKUP formula, it acts as the lookup array criteria, scanning the table column specifically to identify cells that actually contain data.
