Excel手术器械产品列表文本匹配公式优化咨询

Let's break down why your current formulas are falling short and walk through actionable fixes to get way better match rates for your 20k+ instrument inventory:

First, Why Your Existing Formulas Miss the Mark #

• Your first formula relies on LEFT(A2,5), which is extremely fragile. For example, a query for "Curved Scissors" and a product named "Scissors Curved" have completely different first 5 characters—so no match will ever be found, even though they're the same item.
• Your second formula uses wildcard concatenation ("*"&E2&"*"&F2&"*"), but it fails if the order of keywords in the product name doesn't match your manually extracted categories. If a product is listed as "Sharp Curved Scissors" but your formula looks for "ScissorsSharpCurved", it won't find a match because the keyword order is reversed.

Step-by-Step Fixes to Boost Accuracy #

1. Standardize Both Product Names and Queries #

First, clean up your product list to eliminate inconsistencies that throw off matches:

• Normalize text: Use this formula to fix capitalization, remove extra spaces, and expand common abbreviations (adjust substitutions for your inventory's shorthand):

  =TRIM(PROPER(SUBSTITUTE(SUBSTITUTE(G2, "Ang.", "Angled"), "Blnt", "Blunt")))
  

  Store this cleaned text in a new column (e.g., Products!I:I)—this will be your primary matching column.
• Extract standardized dimensions: For size values (10mm, 24cm), use Excel 365's REGEXEXTRACT to pull consistent size strings (avoids fuzzy matches that confuse "10mm" with "100mm"):

  =REGEXEXTRACT(G2, "\d+(\.\d+)?\s*(mm|cm|inch|in)")
  

  Store this in Products!H:H for exact size matching later.

2. Automatically Extract Key Features from Queries (No Manual Category Entry!) #

Stop manually typing categories into E2/F2/G2—build a keyword reference table (e.g., a new sheet named Keywords) with your 4 categories:

Then use array formulas to auto-extract matching keywords from your query cell (e.g., A2):

• Extract Category1 keyword (press Ctrl+Shift+Enter for pre-365 Excel; 365 users can just hit Enter):

  =TEXTJOIN("*", TRUE, IF(ISNUMBER(SEARCH(Keywords!$A$2:$A$10, A2)), Keywords!$A$2:$A$10, ""))
  

• Repeat this formula for Categories 2-4, storing results in cells H2-K2.

3. Use Weighted Scoring to Pick the Best Match (Not Just the First Match) #

Instead of MATCH which grabs the first wildcard match, calculate a relevance score for each product to prioritize the most accurate result:

• Assign weights based on feature importance (e.g., Category1 = 5 points, Category4 = 4 points, Category2 = 3, Category3 = 2—adjust based on your inventory's priorities)
• Use SUMPRODUCT to calculate scores, then pull the product with the highest score:

  =INDEX(Products!I:I, MATCH(MAX(SUMPRODUCT(--ISNUMBER(SEARCH($H$2:$K$2, Products!I:I)) * {5,3,2,4})), SUMPRODUCT(--ISNUMBER(SEARCH($H$2:$K$2, Products!I:I)) * {5,3,2,4}), 0))
  

  This formula scores every product, finds the highest relevance score, and returns the corresponding cleaned product name.

4. Multi-Conditional Filtering for Precise Queries #

For queries with clear multiple features (e.g., "Sharp Curved 10mm Scissors"), use FILTER (Excel 365+) to narrow down exact matches across all features:

=INDEX(FILTER(Products!I:I, 
  ISNUMBER(SEARCH("Scissors", Products!I:I)) *
  ISNUMBER(SEARCH("Curved", Products!I:I)) *
  ISNUMBER(SEARCH("Sharp", Products!I:I)) *
  ISNUMBER(SEARCH("10mm", Products!I:I))), 1)

If multiple products match, combine this with the weighted scoring method to pick the most relevant one.

Final Notes #

• Test with your most problematic queries first to adjust weights and keyword substitutions for your specific inventory.
• For older Excel versions without REGEXEXTRACT or FILTER, use TEXTBEFORE/TEXTAFTER for size extraction, or explore VBA for more advanced fuzzy matching (but the above formulas work for most modern Excel setups).

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