How to Match ZIP Codes Across Multiple Columns in Excel (Return 1 or 0)
Question details
The user needs an Excel formula to check if a specific ZIP code from one sheet exists in any of three columns on another sheet, returning a 1 (found) or 0 (not found).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cross-referencing an Entrance Survey sheet against a master list of DMV ZIP codes that are distributed across three distinct columns.
- Observed behavior
- The standard VLOOKUP formula fails because it only searches the first column of a range, and mismatched data types between the sheets are causing false negatives.
Verify that both sheets format the ZIP codes identically (either both as Text or both as Numbers) to prevent mismatches, especially for ZIP codes with leading zeros.
Use the COUNTIF Function for Exact Matches
The COUNTIF function is the most reliable and efficient way to search for an exact match across a multi-column range.
VLOOKUP and XLOOKUP are designed to search a single column or row. When you need to check if a value exists anywhere within a broader range (like three columns), COUNTIF evaluates the entire grid at once.
By wrapping the condition in a double unary operator (--), we force Excel to convert the TRUE/FALSE result into a 1 or 0.
Click on the first cell in your Entrance Survey sheet where you want the 1 or 0 to appear (e.g., D2).
Type the formula: =--(COUNTIF('DMV Zips'!$A$1:$C$287, C2)>0) and press Enter.
Click the small square at the bottom right of the target cell and drag it down to fill the formula for all remaining rows.
Use ISNUMBER and FIND for Partial String Matches
Use this array formula if your ZIP codes might be embedded within larger text strings (e.g., full addresses).
Analyze Cross-Sheet Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical, logical, and lookup functions like COUNTIF, XLOOKUP, and array formulas. Easily verify cross-column data and maintain 100% compatibility with Microsoft Excel formats.
- 1. Open your data: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 2. Format data consistently: Highlight your ZIP code columns, right-click, select 'Format Cells', and ensure both sheets are formatted as Text.
- 3. Enter the formula: Click your results column and input the exact COUNTIF formula: =--(COUNTIF('DMV Zips'!$A$1:$C$287, C2)>0).
- 4. Fill the results: Drag the fill handle to automatically apply the 1/0 flags to the rest of your survey data.
Frequently Asked Questions
Why does my VLOOKUP return an error when checking multiple columns?
VLOOKUP is designed strictly to search the first leftmost column of a specified range. To search across multiple columns simultaneously, you must use alternative functions like COUNTIF, or combine INDEX and MATCH with array formulas.
How do I keep leading zeros in my ZIP codes?
By default, spreadsheet software treats numbers starting with zero as standard integers and removes the leading zero. To prevent this, select the cells, format them as 'Text' before typing or pasting your data, or apply a custom number format like '00000'.
What does the double dash (--) mean in an Excel formula?
The double dash is known as a double unary operator. It forces logical boolean values (TRUE or FALSE) to convert into their numeric equivalents (1 or 0), making it extremely useful when you need a binary output for statistical analysis.
