Function Problems

How to Return All Matching Values from Another Excel Worksheet

Huma Ashraf Ch Oct 16, 2026 870 views

Question details

The user needs to retrieve every record associated with a lookup value that appears in multiple rows on another worksheet, bypassing the single-match limitation of standard lookup formulas.

How to Return All Matching Values from Another Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Pulling complete historical data or multiple transaction records tied to a single customer ID or code from a separate dataset worksheet.
Observed behavior: Standard lookup formulas like VLOOKUP or INDEX/MATCH only return the first matching value, ignoring any subsequent occurrences in the data.

Before you start

Verify your current spreadsheet software version, as the most efficient method depends on whether your application supports modern dynamic array functions like FILTER.

Solution 1Recommended

Use the FILTER Function (Modern Excel)

The FILTER function is the most efficient and recommended way to extract multiple matching records dynamically without complex array formulas.

The FILTER function automatically 'spills' all matching results into neighboring empty cells. You only need to enter the formula in the top-left cell where you want the results to begin.

Select the Destination Cell

Click on the empty cell in your current worksheet where you want the extracted list of matching values to begin.

Input the FILTER Formula

Type the formula =FILTER(Sheet1!B:B, Sheet1!A:A=A2). In this example, 'Sheet1!B:B' is the column containing the values you want to return, 'Sheet1!A:A' is the column being searched, and 'A2' is your lookup value.

Execute the Function

Press Enter. The formula will instantly retrieve and display all matching records from the specified worksheet.

Prevent Spill Errors: Ensure there are enough empty cells below your formula to accommodate the extracted data. If existing data blocks the results, you will receive a #SPILL! error.

Solution 2

Use an INDEX, SMALL, and IF Array Formula (Older Excel Versions)

For older versions of Excel that lack dynamic array functions, combine INDEX, SMALL, and IF to forcefully extract successive matches one row at a time.

Efficient Data Processing

Easily Extract Multiple Matches in WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions, making complex data retrieval tasks like pulling all matching values fast, seamless, and incredibly simple.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your primary data and your target lookup worksheet.
2. Select the Target Cell: Click on the cell where you want your list of matching records to be generated.
3. Apply the FILTER Function: Type =FILTER(Sheet1!B:B, Sheet1!A:A=A2), making sure to adjust the sheet name and column ranges to match your specific workbook structure.
4. Press Enter to Spill Data: Hit Enter. WPS Spreadsheet will automatically process the dynamic array and spill all matching results down the column.

Fully compatible with Microsoft Excel formulas and functionsNatively supports advanced dynamic array functions like FILTERLightweight, fast execution even with large datasetsFree to download with an intuitive, familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP only return one value?

VLOOKUP is designed by default to scan a column from top to bottom and stop searching the moment it finds the very first exact or approximate match. It does not possess the native capability to continue searching and return an array of multiple matches.

What does a #CALC! error mean when using the FILTER function?

A #CALC! error usually appears if the FILTER function results in an empty array, meaning no records in your dataset matched the specified lookup criteria. You can handle this gracefully by adding the optional [if_empty] argument, like: =FILTER(A:B, A:A=C1, "No Matches Found").

Can I return multiple columns at once using the FILTER function?

Yes. By simply expanding the return array range in the formula (for example, using Sheet1!B:D instead of just Sheet1!B:B), the FILTER function will pull all corresponding columns for the matched rows simultaneously.

Why isn't my INDEX/SMALL/IF formula working correctly?

In legacy versions of spreadsheet software, this specific formula requires evaluation as an array formula. Ensure you are confirming the input by pressing Ctrl+Shift+Enter rather than just Enter. Additionally, double-check that your reference to ROW(A1) at the end of the formula is accurately pointing to the first row to ensure the 'SMALL' function increments correctly as you drag it down.

Excel & Data Problems