Formula Errors

How to Link Excel Data Starting After the First Zero Value

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to extract and link data to another sheet starting from the row immediately following the first occurrence of a zero value in a specific column, ensuring compatibility with older software versions that lack the FILTER function.

Product: Excel
Device & OS: not provided
Scenario: Retrieving specific row data and placing it on another sheet based on the occurrence of the first zero value in a dataset.
Observed behavior: The user needs a reliable formula to locate the first zero value and pull the subsequent data rows downward, specifically looking for an alternative to the FILTER function which is unavailable in Excel 2019.

Before you start

Ensure your dataset is organized in standard columns and note the exact range of your data, as you will need to adjust the cell references in the INDEX and MATCH formulas accordingly.

Solution 1Recommended

Use INDEX and MATCH Formulas to Retrieve Data

This method uses a combination of INDEX, MATCH, and ROW functions to locate the first zero and extract the rows that follow. It is fully compatible with older versions like Excel 2019.

Since newer dynamic array functions like FILTER are not available in Excel 2019, combining INDEX and MATCH provides a robust workaround. This approach locates the position of the first zero and uses a row offset to extract subsequent data step-by-step.

Locate the target cell

Click on the cell in the new sheet where you want the extracted data to begin.

Enter the first INDEX and MATCH formula

Type the formula =IFERROR(INDEX($A$1:$A$15,MATCH(0,$B$2:$B$15,0)+ROW(A1)),"") to retrieve data from column A. Adjust the ranges $A$1:$A$15 and $B$2:$B$15 to match your actual dataset.

Extract adjacent column data

To extract corresponding data from column B, enter =IFERROR(INDEX($B$1:$B$15,MATCH(0,$B$2:$B$15,0)+ROW(A1)),"") in the adjacent cell.

Fill down the formulas

Select the cells containing the newly entered formulas, click the fill handle at the bottom right corner, and drag it down to populate the remaining rows until the cells appear blank.

Understanding the Formula: The MATCH function finds the relative position of the first zero value. The ROW(A1) function acts as an incremental counter, increasing by one for each row you drag down, which moves the INDEX reference to the next row.

Advanced Formula Support

Easily Manage Complex Formulas with WPS Office

WPS Spreadsheet fully supports advanced functions like INDEX, MATCH, and IFERROR, allowing you to manipulate and extract your data efficiently. It is a powerful alternative that handles standard and complex formulas flawlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the dataset.
2. Apply the INDEX and MATCH formula: Enter your combined INDEX and MATCH formula exactly as you would in standard spreadsheet software to locate the target value.
3. Drag to fill: Click and drag the fill handle downwards to extract all subsequent rows automatically without formula errors.

Fully compatible with Microsoft Excel formulas and the .xlsx formatIntuitive interface for managing complex datasets and cross-sheet referencesLightweight and fast performance on all devicesFree built-in data analysis tools and templates

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use IFERROR?

The IFERROR function is used to return a blank cell ("") instead of an error message (#N/A or #REF!) when the formula is dragged down past the available data rows.

Can I use the FILTER function instead?

Yes, if you are using Microsoft 365, Excel 2021, or updated versions of WPS Office, you can use the FILTER function for a simpler and more dynamic solution. However, this INDEX and MATCH method is required for compatibility with Excel 2019 and older versions.

What if my target start value is not zero?

You can easily modify the MATCH function. Simply change the 0 in the MATCH(0,$B$2:$B$15,0) formula to your specific target value, such as a different number or a text string enclosed in quotation marks.

Excel & Data Problems