Use Excel INDEX and MATCH to Find a ZIP Code in Text
Question details
Find a ZIP code within a text string and return the related system from another column.

- 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.
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.
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.
Click an empty cell where you want the matching system to appear.
Type =INDEX($B$2:$B$2001,MATCH("*"&D3&"*",$A$2:$A$2001,0)) and press Enter.
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.

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. Open the spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
- 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. 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. Verify and save: Check the returned system against the matching row, then choose File > Save to save the workbook.

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.




