How to Return the Last Nonblank Value in a Variable Excel Column
Question details
The user needs to retrieve the last displayed or nonblank value in an Excel column that dynamically changes length based on other formulas or inputs.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting the final value from a dynamic data column, such as a mortgage payment schedule, where trailing rows may become blank or hidden due to formula recalculations.
- Observed behavior
- The target cell needs to dynamically update to show the last visible data point without capturing empty cells generated by underlying formulas.
Determine whether your target column contains purely numeric data (like dates or currency), exclusively text, or a mixture of both, as this will dictate which formula you should use.
Find the Last Numeric Value Using LOOKUP
Use the LOOKUP function with an intentionally large number to find the last cell containing a numeric value or date.
The LOOKUP function ignores errors and empty cells. By searching for an extremely large number (9.99E+307, the largest number Excel can handle), LOOKUP will bypass empty formula results and default to the last numeric value in the specified range.
Click on the cell where you want the final extracted value to be displayed.
Type =LOOKUP(9.99999999999999E+307, K:K) into the formula bar, replacing K:K with your actual target column.
Press Enter. The cell will now dynamically display the last numeric value or date present in the column.
Find the Last Text Value Using LOOKUP
Use a similar LOOKUP method with the REPT function to find the last cell containing a text string.
Find the Last Value Regardless of Data Type Using INDEX and MAX
If your column contains a mix of numbers, text, and formula-generated blanks, use an array formula to evaluate and find the last non-empty row.
Handle Dynamic Data Easily with WPS Spreadsheet
WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced LOOKUP, INDEX, and dynamic array formulas. You can seamlessly calculate variable-length schedules without dealing with broken formulas.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office, click on 'Spreadsheet', and open your financial or mortgage document.
- 2. Select the result cell: Click the empty cell where you want to return the last visible value of your table.
- 3. Insert the lookup formula: Type the formula =LOOKUP(9.99999999999999E+307,K:K) directly into the formula bar and press Enter.
- 4. Watch the data update dynamically: As you change inputs and the column shortens, WPS Spreadsheet will instantly recalculate and display the new final value.
Frequently Asked Questions
Why does my formula return a zero instead of the last value?
This usually happens if the formula references a cell that contains a formula returning an empty string ("") or a zero value. Ensure you are using the INDEX and MAX array formula if your column contains formula-generated blank cells, as standard LOOKUP might capture those empty strings.
Does the LOOKUP 9.99E+307 trick work for dates?
Yes. In spreadsheets, dates are stored as underlying numeric values (serial numbers). The LOOKUP formula searching for an extremely large number will successfully find and return the last date in a column.
How can I find the last visible value if rows are manually hidden?
The standard LOOKUP and INDEX formulas do not differentiate between manually hidden rows and visible rows. To ignore manually hidden rows, you must use a combination involving the AGGREGATE or SUBTOTAL functions, which are designed to explicitly ignore hidden rows.
