How to Filter Excel Data by Brand Names on Another Worksheet
Question details
The user wants to filter a dataset based on a list of brand names stored in a named range and output the filtered results onto a different worksheet.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting specific records from a master dataset to a separate sheet based on a predefined list of criteria (brand names).
- Observed behavior
- The user needs a reliable method to accurately filter and transfer data without encountering invalid name errors, as some defined names in their sample workbook were returning invalid results.
Ensure your master dataset has clear column headers and that your list of brand names is correctly defined as a Named Range without any invalid references or blank cells.
Use the FILTER and XMATCH Functions
This dynamic array formula automatically filters and spills the matching data onto your new worksheet, updating instantly when data changes.
This method requires a modern spreadsheet version that supports dynamic array formulas. It is the most efficient way to cross-reference a master data table against a named range.
Open the separate worksheet where you want the filtered data to appear and select the top-left cell for your new table.
Type the formula =FILTER(DataRange, ISNUMBER(XMATCH(BrandRange, BrandList))) substituting 'DataRange' with your master data, 'BrandRange' with the brand column in the master data, and 'BrandList' with your named range of criteria.
Hit Enter on your keyboard. The formula will dynamically pull all rows matching the brand names into your new worksheet.
Extract Data Using Advanced Filter
If you are using a version of Excel that does not support dynamic array formulas, the Advanced Filter tool is the best built-in alternative for extracting data.
Effortlessly Filter and Manage Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions and data filtering tools, making it incredibly easy to extract data based on brand lists across worksheets. It provides a seamless experience for managing large datasets with complex criteria.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and ensure your named ranges are active via the Formulas > Name Manager tab.
- 2. Apply the FILTER formula: On your blank destination sheet, use the =FILTER(A2:D100, COUNTIF(BrandList, B2:B100)>0) or XMATCH equivalent to dynamically pull data.
- 3. Save and share: Press Enter to view your filtered list, then save your file in .xlsx format to ensure seamless cross-platform compatibility.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! or #NAME? error?
A #CALC! error usually means no records in the master data matched your brand names list. A #NAME? error occurs if your spreadsheet software version doesn't support the FILTER/XMATCH functions, or if the Named Range ('BrandList') is misspelled or invalid.
How do I fix invalid defined names in my workbook?
Go to the Formulas tab and click 'Name Manager'. Review your named ranges to ensure the 'Refers to' field points to the correct cells. Delete or edit any names showing a #REF! error.
Can I use VLOOKUP instead to filter data by a list of names?
VLOOKUP is designed to return a single matching value rather than filtering multiple rows. To extract all records matching a list of brand names, the FILTER function or the Advanced Filter tool are the correct features to use.
Will Advanced Filter update automatically if the source data changes?
No, the Advanced Filter tool extracts a static copy of the data at the time you run it. If your master data or brand list changes, you will need to run the Advanced Filter again or switch to a dynamic array formula like FILTER.
