Function Problems

How to Use Excel FILTER to Return All Matching Postal Codes

Elise Williams Oct 8, 2026 872 views

Question details

The user needs to retrieve and display a list of all postal codes associated with a specific province, overcoming the limitation of VLOOKUP which only returns the first matched result.

How to Use Excel FILTER to Return All Matching Postal Codes

Product: Spreadsheet
Device & OS: not provided
Scenario: Looking up and extracting multiple matching data entries from a larger dataset table into a separate list.
Observed behavior: Using VLOOKUP only returns the first instance of a matched province, completely omitting any subsequent postal codes associated with that same province.

Before you start

Ensure your dataset is organized into clear columns (e.g., 'Province' and 'Postal Code') and that your spreadsheet software supports modern dynamic array functions like FILTER and UNIQUE.

Solution 1Recommended

Use the FILTER and UNIQUE Dynamic Array Formula

Combine the FILTER and UNIQUE functions to extract all matching records automatically and remove any exact duplicates from the final results.

The FILTER function extracts all records meeting your specific criteria, solving the primary limitation of standard lookup functions. Wrapping this formula inside the UNIQUE function ensures that if there are identical postal code entries for a single province, they only appear once in your generated list.

Select the target cell

Click on the empty cell where you want the resulting list of postal codes to start.

Enter the FILTER function

Type =FILTER(TableName[Postal Code], TableName[Province]=N2) into the formula bar. Replace 'TableName' with your actual table reference, and 'N2' with the cell containing the specific province you are searching for.

Wrap the formula with UNIQUE

Modify the formula to =UNIQUE(FILTER(TableName[Postal Code], TableName[Province]=N2)) to automatically filter out duplicate postal codes from the returned data array.

Press Enter to spill results

Press Enter on your keyboard. The formula will dynamically 'spill' down the column, instantly displaying all matching postal codes.

Use the FILTER and UNIQUE Dynamic Array Formula

Dynamic Array Spilling: Make sure there are completely empty cells below your active cell. If any existing data or text blocks the output range, the formula will return a #SPILL! error.

Powerful Spreadsheet Tool

Easily Filter Multiple Results with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER and UNIQUE out of the box. You can effortlessly retrieve, sort, and analyze massive datasets without writing complex macros.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the postal code and province columns.
2. Input the dynamic array formula: Click an empty cell and enter the formula =UNIQUE(FILTER(B2:B100, A2:A100="Alberta")), adjusting the cell ranges to fit your specific table dimensions.
3. Get instant spilled results: Press Enter to instantly spill all matching, unique postal codes into the adjacent cells downwards.

Fully compatible with Microsoft Excel formulas and array functions, ensuring your current workbooks operate seamlessly.Lightweight software architecture that processes large data tables and complex array calculations instantly.Intuitive user interface for managing named ranges, data validation, and advanced data filtering.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when using the FILTER function?

A #SPILL! error occurs when the dynamic array formula attempts to output multiple results, but the required cells below it contain text, data, or spaces. Clear the blocking cells to resolve the error and allow the array to display.

Can I use the FILTER function with multiple criteria at the same time?

Yes, you can filter by multiple criteria using an asterisk (*) for 'AND' logic or a plus sign (+) for 'OR' logic. For example: =FILTER(A2:A10, (B2:B10="Alberta") * (C2:C10>100)) returns data matching both conditions.

What should I do if no matching postal codes are found by the formula?

You can provide a fallback value for empty results by utilizing the optional third argument of the FILTER function. For example: =FILTER(A2:A10, B2:B10="Unknown Province", "No Match Found") will display 'No Match Found' instead of a #CALC! error.

Is there an alternative method if my software version doesn't support the FILTER function?

If dynamic arrays are not supported in your current spreadsheet version, you will traditionally need to use a complex INDEX/MATCH array formula combined with the SMALL and ROW functions, or simply use Pivot Tables to group and extract the relevant data.

Excel & Data Problems