How to Return the Last Non-Blank Value from a Horizontal Excel Range
Question details
The user wants to automatically populate a specific cell with the last non-blank value found within a designated horizontal range (e.g., G55:R55) using a formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting the final piece of recorded data (like the latest monthly sales figure) from a row that may contain scattered empty cells.
- Observed behavior
- A dynamic formula is required to identify and display the last populated cell's content while completely ignoring any blank cells in the designated row.
Ensure you are using a spreadsheet program or version that supports modern dynamic array functions like TAKE and TOROW (such as Microsoft 365). For older versions, alternative classic formulas will be required.
Use Microsoft 365 Dynamic Array Formulas (TAKE and TOROW)
Utilize the modern TAKE and TOROW functions to easily ignore blanks and extract the final value in a row.
The TOROW function is designed to convert an array or range into a single row, and it features built-in arguments to skip empty cells. By wrapping it in the TAKE function, you can effortlessly grab the very last element of the resulting array.
Click on the empty cell where you want the extracted last value to appear (for example, B6).
Type the formula =TAKE(TOROW(G55:R55,3),,-1). Ensure you replace 'G55:R55' with your actual target range if it differs.
Press Enter. The cell will calculate and instantly display the last non-blank value from your selected horizontal range.
Use the Classic LOOKUP Function (Compatible with Older Versions)
If you are not using Microsoft 365 and lack access to TAKE and TOROW, use the classic LOOKUP function to find the last non-blank value.
Extract Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools and comprehensive formula support, making it simple to manipulate horizontal arrays and extract exact values effortlessly.
- 1. Open your Workbook in WPS: Launch WPS Office and open your existing spreadsheet file containing the horizontal data.
- 2. Select the Destination Cell: Click on the cell where you want to output the last non-blank value.
- 3. Apply the Formula: Input a compatible extraction formula such as =LOOKUP(2,1/(G55:R55<>""),G55:R55) and press Enter to instantly retrieve the final value.
Frequently Asked Questions
Why does the TAKE and TOROW formula return a #NAME? error?
This error occurs if your version of Excel does not support dynamic array functions like TAKE and TOROW. These functions are primarily available in Microsoft 365 and newer desktop versions. For older versions, use the LOOKUP method instead.
How do I return the last value from a vertical column instead of a horizontal row?
For a vertical column, you can use a similar approach by swapping TOROW with TOCOL. The formula would be =TAKE(TOCOL(A1:A50, 3), -1) for a column range.
Does the LOOKUP formula work for both text and numbers?
Yes, the classic formula =LOOKUP(2,1/(Range<>""),Range) successfully identifies and returns the last non-blank cell regardless of whether it contains alphabetical text, symbols, or numerical data.
