Function Problems

How to Create a List from Rows with Matching Values Using FILTER and VLOOKUP

Muhammad Talha Sep 25, 2026 870 views

Question details

Extract specific material numbers and descriptions based on matching load criteria, and subsequently retrieve associated pallet quantities.

How to Extract Data from Rows with Matching Values in Spreadsheets

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering a large raw dataset to generate a clean, consolidated list of items and their respective quantities based on specific matching conditions.
Observed behavior: Instead of manually copying data, the goal is to automatically return relevant rows using array formulas and lookup functions.

Before you start

Ensure your dataset has clear column headers and contains a unique identifier (such as a material number) to serve as a reliable lookup key for the VLOOKUP function.

Solution 1Recommended

Use FILTER and VLOOKUP to Extract and Match Data

Combine the dynamic array capabilities of the FILTER function with VLOOKUP to automatically generate a list of matching records and their corresponding details.

The FILTER function is ideal for extracting multiple rows of data that meet a specific criteria, such as a matching load value. Once the base data is extracted, VLOOKUP can be used to pull in related metrics like pallet quantities from another table or column.

Apply the FILTER Function

Select the top cell of your destination range and enter a formula like =FILTER(A2:B100, C2:C100="Target Load Value"). This will automatically spill the matching material numbers and descriptions into adjacent cells.

Verify the Lookup Key

Check that the extracted material number (now in your new list) perfectly matches the formatting of the unique identifier in your source data's DU column.

Retrieve Quantities with VLOOKUP

In the column next to your filtered results, enter =VLOOKUP(E2, Data_Range, Column_Index, FALSE) where E2 is your newly filtered material number. This fetches the matching pallet quantity.

Copy the Lookup Formula

Drag the fill handle of the cell containing your VLOOKUP formula down to apply it to all the rows generated by your FILTER function.

Use FILTER and VLOOKUP to Extract and Match Data

Dynamic Spill Range: Because FILTER is a dynamic array function, it requires empty cells below it to display results. Ensure the destination area is clear to avoid a #SPILL! error.

Smart Data Processing

Easily Manage Data Arrays with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas including FILTER, as well as classic functions like VLOOKUP. Process heavy datasets quickly with a familiar, user-friendly interface.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your raw data tables.
2. Input the FILTER formula: Select an empty cell and use the =FILTER() function to define your array and extraction criteria.
3. Complete with VLOOKUP: In the adjacent column, reference the spilled array results using =VLOOKUP() to pull in the final quantities.

Fully compatible with Microsoft Excel formulas and array functionsSeamlessly handles #SPILL! dynamic arrays for automated reportingLightweight application that prevents lagging when processing large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER function returning a #CALC! error?

A #CALC! error typically means the FILTER function could not find any rows that match your criteria. You can prevent this error by providing a default value in the third argument, such as =FILTER(A2:B10, C2:C10="Target", "No matches found").

Can I use XLOOKUP instead of VLOOKUP for this task?

Yes, if your spreadsheet version supports XLOOKUP, it is often a safer alternative. XLOOKUP can search in any direction and defaults to an exact match, whereas VLOOKUP requires the lookup key to be in the leftmost column.

How do I fix a #SPILL! error when using the FILTER function?

The #SPILL! error occurs when there is existing data, text, or even a hidden space in the cells where the FILTER function is trying to display its results. Simply clear the cells below and to the right of your formula to resolve it.

Can I filter data based on multiple criteria at once?

Yes, you can use multiplication (*) for AND logic, or addition (+) for OR logic within the FILTER criteria. For example, =FILTER(A2:B10, (C2:C10="Load A")*(D2:D10="In Stock")) will return rows that meet both conditions.

Excel & Data Problems