Excel技术咨询：英国邮编校验公式假阳性问题排查

Hey there! Let’s troubleshoot that frustrating false positive issue you’re hitting with your UK postcode checks. False positives almost always happen when your formula is doing partial matching instead of precise, intentional matching—especially easy to miss with UK postcodes since they have a consistent but nuanced structure.

First, Let’s Diagnose the Root Cause #

Chances are the formulas you tried (like =OR(ISNUMBER(SEARCH(your_postcode_list,A1))) or =SUMPRODUCT(--(ISNUMBER(SEARCH(your_postcode_list,A1))))>0) rely on partial matching. For example:

• If your saved list has SW1, the formula will flag SW10 9JT as TRUE (since "SW1" is part of "SW10")—even though it’s not actually in your list. That’s the false positive you’re seeing.

Solutions Based on Your Use Case #

Pick the formula that fits whether you’re checking full postcodes or postcode prefixes:

1. Matching Full UK Postcodes (Exact Match)

These formulas will only return TRUE if the entire postcode exists in your saved list:

• Using COUNTIF (works in all Excel versions):

  =COUNTIF($B$2:$B$100, A1) > 0
  

  Replace $B$2:$B$100 with your saved postcode range. COUNTIF does exact matches by default (no partial hits unless you use wildcards).
• Using XLOOKUP (Excel 365/2021+):

  =NOT(ISERROR(XLOOKUP(A1, $B$2:$B$100, A1, "")))
  

  This looks for an exact match and returns TRUE if found, FALSE otherwise.
• Using MATCH:

  =NOT(ISNA(MATCH(A1, $B$2:$B$100, 0)))
  

  The 0 in MATCH forces an exact match; we convert the #N/A (not found) result to FALSE.

2. Matching Postcode Prefixes (e.g., SW1, EC2)

If your saved list only has prefixes (the first part before the space), you need to extract the prefix from your target cell first to avoid partial matches:
First, extract the prefix (handles postcodes with or without spaces):

=LEFT(IF(ISNUMBER(FIND(" ", A1)), A1, LEFT(A1, LEN(A1)-3) & " " & RIGHT(A1, 3)), FIND(" ", IF(ISNUMBER(FIND(" ", A1)), A1, LEFT(A1, LEN(A1)-3) & " " & RIGHT(A1, 3))) - 1)

Then wrap it in a COUNTIF to check against your prefix list:

=COUNTIF($B$2:$B$100, LEFT(IF(ISNUMBER(FIND(" ", A1)), A1, LEFT(A1, LEN(A1)-3) & " " & RIGHT(A1, 3)), FIND(" ", IF(ISNUMBER(FIND(" ", A1)), A1, LEFT(A1, LEN(A1)-3) & " " & RIGHT(A1, 3))) - 1)) > 0

Quick Checks to Avoid Future Issues #

• Format Consistency: Make sure your saved list and target postcodes have the same format (e.g., with spaces, no extra spaces at the start/end). Use TRIM(A1) to clean up any stray spaces.
• Wildcards: Double-check your saved list for accidental wildcards (* or ?)—these will cause partial matches even with exact-match formulas.
• Case Sensitivity: If you need case-sensitive matches (uncommon for UK postcodes), use:

  =SUMPRODUCT(--EXACT($B$2:$B$100, A1)) > 0
  

Example to Test #

Suppose:

• A1 = SW10 9JT
• Your saved list has only SW1 0AA
• Old partial-match formula: Returns TRUE (false positive)
• New exact-match formula: Returns FALSE (correct result)

内容的提问来源于stack exchange，提问作者OJR