How to Extract the Last 52 Non-Blank Values in Excel Using Formulas
Question details
The user needs an Excel formula to retrieve exactly the most recent 52 non-blank values from a specific column range (H12:H264).
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Extracting a fixed number of recent non-empty data points from a large, partially populated dataset for reporting or analysis.
- Observed behavior
- A customized array formula is required to skip blank cells and continuously return only populated values up to a count of 52.
Identify the exact data range containing your values (e.g., H12:H264) and select a clean output column with at least 52 empty rows to place the results.
Use INDEX, SMALL, and SEARCH Array Formula
This method utilizes a complex array formula to evaluate non-blank cells, returning them sequentially in a new column.
The formula combines INDEX, SMALL, SEARCH, and IFERROR to filter out empty cells. SEARCH looks for any character, and when combined with the row number, it allows SMALL to pull the smallest matching row indices one by one.
Select the first cell in your output column (for example, J12) where you want the extracted list to begin.
Type the formula: =IFERROR(INDEX($H$12:$H$264,SMALL(IFERROR(SEARCH("?",$H$12:$H$264),100000)*ROW($H$12:$H$264),ROW(J1))-ROW($H$11)),"") into the formula bar.
If you are using an older version of Excel, you must press Ctrl+Shift+Enter to confirm it as an array formula. In Office 365, pressing Enter is sufficient.
Click the fill handle in the bottom-right corner of the cell and drag it down 52 rows to display the remaining non-blank values.
Extract Data Effortlessly in WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet application where complex array formulas like INDEX and SMALL work flawlessly. You can easily manage large datasets, skip blank cells, and extract exact data points with familiar logic.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Paste the Formula: Select your destination cell and paste the provided INDEX, SMALL, and SEARCH formula.
- 3. Confirm Array: Press Ctrl+Shift+Enter to correctly apply the array calculation.
- 4. Drag to Extract: Drag the fill handle down 52 rows to extract the desired non-blank values instantly.
Frequently Asked Questions
Why does the formula only show blank results?
The IFERROR function at the beginning of the formula forces it to return a blank if an error occurs. This usually happens if you haven't confirmed it with Ctrl+Shift+Enter in older versions, or if the referenced data range contains no non-blank values.
Can I use this formula to return more or fewer than 52 values?
Yes, the number of values returned depends entirely on how far you copy the formula down the column. Dragging it down 10 rows will return 10 non-blank values, while dragging it down 100 rows will attempt to return 100.
What does the '?' wildcard in the SEARCH function do?
The question mark is a wildcard character that matches any single text character. It ensures the SEARCH function successfully identifies populated cells, effectively bypassing the completely blank ones in your dataset.
Does this array formula work in WPS Office?
Yes, this formula relies on standard mathematical and lookup functions (INDEX, SMALL, SEARCH, IFERROR) that are fully compatible and function identically in WPS Spreadsheet.
