Function Problems

How to Return Multiple Email Addresses for Matching ZIP Codes in Excel

Phi Hung Vo Oct 10, 2026 870 views

Question details

The user needs to extract multiple email addresses corresponding to specific target ZIP codes across different worksheets without encountering errors.

How to Return Email Addresses for Matching ZIP Codes in Excel

Product: Excel
Device & OS: not provided
Scenario: Matching target county ZIP codes from one worksheet with a master list of volunteer names, emails, and ZIP codes in another worksheet to compile an email list.
Observed behavior: Using VLOOKUP only returns the first matching email address instead of all matches, and incorrect dynamic array usage results in #SPILL! errors.

Before you start

Ensure you are using a version of Excel or WPS Office that supports dynamic array functions like FILTER, and verify that the cells below your target formula cell are completely empty to prevent #SPILL! errors.

Solution 1Recommended

Use the FILTER Function to Extract Multiple Matches

The FILTER function dynamically returns an array of all matching records for a specific criteria, effectively overcoming VLOOKUP's single-match limitation.

VLOOKUP is designed to find a value and stop at the very first match. To return a list of all emails for a single ZIP code, a dynamic array function is required. The FILTER function will pull all rows that meet your criteria and 'spill' them into the adjacent empty cells.

Select the target cell

Click on the cell in your target worksheet where you want the first matching email address to appear (for example, cell E2 next to the target ZIP code in D2).

Enter the FILTER formula

Type the formula =FILTER('Tab 1'!$B$2:$B$6001, 'Tab 1'!$C$2:$C$6001=D2) where Column B contains the emails and Column C contains the ZIP codes.

Wrap with IFERROR for clean results

To prevent error messages when a ZIP code has no matching volunteers, wrap the function like this: =IFERROR(FILTER('Tab 1'!$B$2:$B$6001, 'Tab 1'!$C$2:$C$6001=D2), "").

Execute the formula

Press Enter. The formula will automatically extract and spill all matching email addresses down the column.

Use the FILTER Function to Extract Multiple Matches

Preventing #SPILL! Errors: If you receive a #SPILL! error, it means existing text, spaces, or data are blocking the formula from expanding downwards. Simply delete the obstructing data in the cells below to allow the results to display properly.

Easily Filter and Extract Data with WPS Office

WPS Spreadsheet fully supports modern dynamic array formulas like FILTER, allowing you to seamlessly look up and extract multiple matching records across worksheets. It is a highly efficient, free tool for managing complex datasets like volunteer rosters.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your volunteer and ZIP code data.
2. Navigate to the target cell: Select the empty cell next to the target ZIP code where you want the email list to start.
3. Apply the FILTER function: Input the FILTER formula to define your email range as the return array and the ZIP code range as the inclusion criteria.
4. View spilled results: Press Enter to instantly populate all corresponding email addresses into the cells below.

Fully compatible with Microsoft Excel formulas, including dynamic arraysEasily extract multiple matches without complex VBA scriptsLightweight, fast, and completely free to useIntuitive interface for managing large lists across multiple worksheets

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP only return one email address?

VLOOKUP is designed by default to search for a lookup value and stop searching as soon as it finds the first exact match. To return multiple corresponding matches, you must use dynamic array functions like FILTER or a complex combination of INDEX and AGGREGATE.

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

A #SPILL! error occurs when a dynamic array formula needs to output multiple results into adjacent cells, but one or more of those required cells already contain data (even a hidden space). Clearing the blocked cells will resolve the error instantly.

How do I handle instances where a ZIP code has no matching emails?

You can use the built-in [if_empty] argument in the FILTER function to handle blank results. For example: =FILTER(B2:B100, C2:C100=D2, "No Match Found"). Alternatively, wrapping the whole formula in an IFERROR function works similarly.

Excel & Data Problems