Function Problems

How to Extract Names When a Status is Passed in Excel

Tauseeq Magsi Oct 8, 2026 871 views

Question details

The user needs an Excel formula to extract data from one column (names) to another worksheet based on a specific conditional status (e.g., 'Passed') located in a different column.

How to Extract Names When a Status is Passed in Excel

Product: Excel
Device & OS: not provided
Scenario: Pulling and transferring specific records dynamically into a new worksheet when they meet a defined text criterion in the source data.
Observed behavior: The user requires an automated formula-based approach to filter and extract matching names without manually copying and pasting the data.

Before you start

Ensure your version of the spreadsheet software supports dynamic array formulas like FILTER. Make sure the destination worksheet has enough empty rows to accommodate the extracted data to prevent a #SPILL! error.

Solution 1Recommended

Use the FILTER Function to Extract Names

The FILTER function is the most efficient and dynamic way to pull data based on conditions across worksheets.

The FILTER function allows you to filter a range of data based on criteria you define. By referencing the original worksheet in your formula, you can extract the exact records you need seamlessly.

Select the destination cell

Open the target worksheet where you want the extracted names to appear and click on the starting cell.

Enter the FILTER formula

Type the formula =FILTER(Sheet1!B2:B7, Sheet1!A2:A7="Passed"). Replace 'Sheet1' with the actual name of your source data worksheet, 'B2:B7' with the column containing the names, and 'A2:A7' with the column containing the status.

Press Enter to execute

Press Enter. The formula will automatically spill down the column, displaying all the names that have a 'Passed' status.

Use the FILTER Function to Extract Names

Dynamic Updates: Because this is a dynamic formula, any changes you make to the status column in the original worksheet will automatically update the extracted list on the new worksheet.

Spreadsheet Efficiency

Extract and Filter Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers robust support for advanced array formulas, including the FILTER function, allowing you to extract records based on specific statuses effortlessly across worksheets.

1. Open your workbook in WPS Office: Launch WPS Spreadsheet and open the file containing your status and name columns.
2. Navigate to the destination sheet: Click the '+' icon to create a new worksheet, or select an existing sheet tab where you want the names to appear.
3. Apply the filtering formula: In the target cell, input =FILTER(Sheet1!B2:B7, Sheet1!A2:A7="Passed") and hit Enter.
4. Save your work: Click the Save icon in the top left corner or press Ctrl+S to save your newly automated extraction.

Full support for advanced dynamic array formulas for quick data extraction.Seamlessly compatible with Microsoft Excel (.xlsx) files and formulas.Built-in intuitive data filtering tools for users who prefer visual sorting.Lightweight and runs smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

What if my version of Excel doesn't support the FILTER function?

If you are using an older version of Excel that lacks dynamic arrays, you cannot use the FILTER function. Instead, you can use the built-in 'Advanced Filter' tool under the Data tab, or use a more complex array formula combining IFERROR, INDEX, SMALL, and ROW functions.

How do I extract names for multiple statuses, like 'Passed' or 'Pending'?

You can add multiple conditions in the FILTER function by using the plus sign (+) to represent OR logic. For example: =FILTER(B2:B7, (A2:A7="Passed")+(A2:A7="Pending")).

Why am I getting a #CALC! error with my FILTER formula?

The #CALC! error occurs when the FILTER function finds no records matching your criteria (e.g., no one has a 'Passed' status). You can prevent this error by adding the optional [if_empty] argument at the end of your formula: =FILTER(B2:B7, A2:A7="Passed", "No matches found").

Why does my formula return a #SPILL! error?

A #SPILL! error means the FILTER function does not have enough empty cells below the formula to display all the extracted names. Clear any text or data in the cells directly beneath your formula to allow the results to spill properly.

Excel & Data Problems