Excel两列名称近似匹配问题：VLOOKUP TRUE条件批量匹配报错求助

Got it, let's tackle this head-on—matching inconsistent company names across 50k rows is a common pain point, and VLOOKUP with TRUE isn't cutting it because it relies on approximate matches that only work well with sorted, similarly formatted data. Here are actionable, scalable solutions tailored to your scenario:

The root issue is inconsistent formatting, so cleaning the names first will eliminate most matching errors. Try these steps:

• Normalize Text Case: Convert both columns to the same case (all lowercase or uppercase) to avoid case-sensitive mismatches. Use these formulas:
  =LOWER(A2) for column 1, =LOWER(B2) for column 2
• Strip Common Suffixes/Prefixes: Create a list of overused terms like "Inc", "Incorporate", "Corp", "LLC", "Ltd" and remove them. For a quick fix, nest SUBSTITUTE():
  Example for removing "Inc" and "Incorporate":
  =TRIM(SUBSTITUTE(SUBSTITUTE(LOWER(A2), "inc", ""), "incorporate", ""))
  Pro tip: For a more robust clean, use REGEXREPLACE() (Excel 365/2021 or Google Sheets) to match and remove any suffix pattern in one go:
  =TRIM(REGEXREPLACE(LOWER(A2), "\b(inc|incorporate|corp|llc|ltd)\b", ""))
• Clean Up Extra Spaces/Characters: Use TRIM() to remove leading/trailing spaces and CLEAN() to eliminate hidden non-printable characters:
  =TRIM(CLEAN(LOWER(A2)))

Once both columns are standardized, switch to VLOOKUP(..., FALSE) (exact match)—it’ll be far more reliable for your 50k-row dataset.

If standardizing isn’t feasible right away, use tools that account for partial string similarities:

• Excel 365/2021: XLOOKUP with Wildcards: Combine wildcards to match core company names (like "3M" in your example):
  =XLOOKUP("*"&LEFT(A2,FIND(" ",A2)-1)&"*", B:B, B:B, "No Match")
  This works best if the core identifier of the company is consistent across both columns.
• Power Query Fuzzy Merge: Built for large datasets, this is my go-to for scalable matching:
  1. Load both columns into Power Query (Data > From Table/Range)
  2. Go to Home > Merge Queries > Merge as New
  3. Select the two name columns, choose "Left Outer" as the join kind (to keep all rows from your first column)
  4. Click the dropdown on the merged column > Fuzzy Match Options
  5. Adjust the similarity threshold (start with 0.7-0.8) and enable options like "Ignore case", "Ignore whitespace", "Ignore prefixes/suffixes"
  6. Expand the merged column to get your matching results
• Custom VBA Fuzzy Match: For older Excel versions, use a Levenshtein Distance function to measure string similarity. Here’s a quick snippet:

  Function FuzzyMatch(str1 As String, str2 As String) As Double
      Dim i As Integer, j As Integer, len1 As Integer, len2 As Integer
      Dim matrix() As Integer
      len1 = Len(str1): len2 = Len(str2)
      ReDim matrix(len1, len2)
      For i = 0 To len1: matrix(i, 0) = i: Next
      For j = 0 To len2: matrix(0, j) = j: Next
      For i = 1 To len1
          For j = 1 To len2
              matrix(i, j) = WorksheetFunction.Min(matrix(i - 1, j) + 1, matrix(i, j - 1) + 1, matrix(i - 1, j - 1) + IIf(Mid(str1, i, 1) = Mid(str2, j, 1), 0, 1))
          Next
      Next
      FuzzyMatch = 1 - (matrix(len1, len2) / WorksheetFunction.Max(len1, len2))
  End Function
  

  Use it in a formula like =IF(FuzzyMatch(A2,B2)>=0.8, B2, "No Match") to return matches with 80% or higher similarity.

Quick reality check: VLOOKUP(..., TRUE) is designed for approximate matches on sorted, uniformly formatted data (like numbers or alphabetized text). It doesn’t understand that "3M Inc" and "3M Incorporate" refer to the same company—it’ll just return the closest alphabetical match, which is why you’re seeing errors. Stick to exact matches after standardization or fuzzy matching instead.

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