Function Problems

Use Excel INDEX and MATCH to Find a ZIP Code in Text

Nimra MalikNimra Malik Nov 22, 2026 868 views

Question details

Find a ZIP code within a text string and return the related system from another column.

Find a ZIP Code in Text with Excel INDEX and MATCH
Product
Microsoft Excel
Device & OS
not provided
Scenario
The ZIP code to search for is in D3, the text strings are in column A, and the related systems are in column B.
Observed behavior
The desired result is the system from column B corresponding to a row where column A contains the ZIP code.
Before you start

Check that D3 contains the ZIP code and that the text and system ranges cover the same rows. For ZIP codes with leading zeros, store the search value as text so the zeros are retained.

Solution 1Recommended

Use INDEX and MATCH with wildcard characters

Use this formula to find the ZIP code anywhere within a cell in column A and return the related value from column B.

1
Select a result cell

Click an empty cell where you want the matching system to appear.

2
Enter the formula

Type =INDEX($B$2:$B$2001,MATCH("*"&D3&"*",$A$2:$A$2001,0)) and press Enter.

3
Check the result

Compare the returned value with the row in A2:B2001 that contains the ZIP code from D3. Adjust the ranges if your data extends beyond row 2001.

Use INDEX and MATCH with wildcard characters
How the match works: The asterisks allow text before and after the ZIP code, while 0 enables an exact MATCH with wildcards. The formula returns the first matching row, including a match where the ZIP code is part of a longer number.
Work with spreadsheet formulas

Find ZIP codes and related values in WPS Spreadsheet

Use INDEX and MATCH with wildcards in WPS Spreadsheet to search text for a ZIP code and return its related system.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Check the source cells: Confirm that D3 contains the ZIP code, column A contains the text to search, and column B contains the related systems.
  3. 3. Enter the formula: Select an empty result cell, enter =INDEX($B$2:$B$2001,MATCH("*"&D3&"*",$A$2:$A$2001,0)), and press Enter.
  4. 4. Verify and save: Check the returned system against the matching row, then choose File > Save to save the workbook.
Search text for ZIP codes with INDEX and MATCHOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does MATCH need 0 as its third argument?

The 0 tells MATCH to use an exact match and allows the asterisk wildcards to match text around the ZIP code.

What if more than one row contains the ZIP code?

MATCH returns the position of the first matching row in A2:A2001, so INDEX returns the corresponding value from column B.

Why does the formula return #N/A?

No matching text may exist in A2:A2001, or D3 may not contain the expected ZIP code. Check the search value, leading zeros, and the search range.

How do I preserve a leading zero in a ZIP code?

Format D3 as Text before entering the ZIP code, or type an apostrophe before it, such as '01234. Make sure the text being searched also contains the leading zero.