Excel技术咨询：判断A列随机文本是否包含B列课程词汇及COUNT+SEARCH公式失效问题

Hey there! Let's break down why your original formula isn't working and walk through reliable fixes tailored to different Excel versions.

Why Your Original Formula Fails #

Your =COUNT(SEARCH(D1:D7000,A$1:A$14)) formula has two key issues:

• SEARCH returns an array of position numbers (or #VALUE! errors when no match exists), but COUNT only counts valid numbers—ignoring errors entirely. This means you can't tell if a specific cell in Column A has a match.
• The range reference A$1:A$14 locks you to a fixed range, but you likely want to check each individual row in Column A against all course vocabulary in Column D.

Fix 1: SUMPRODUCT (Works for All Excel Versions) #

This is the most robust solution that works across every Excel version. For cell C1 (to check A1 against your vocabulary list in D1:D7000), use this formula and drag it down Column C:

=SUMPRODUCT(--ISNUMBER(SEARCH(D$1:D$7000,A1)))>0

Here's what each part does:

• SEARCH(D$1:D$7000,A1): Checks if A1 contains each vocabulary term in Column D, returning a position number if found, or an error if not.
• ISNUMBER(...): Converts valid position numbers to TRUE and errors to FALSE.
• --: Converts TRUE/FALSE to 1/0 so we can sum the results.
• SUMPRODUCT: Adds up all the 1s and 0s. If the total is greater than 0, it means at least one vocabulary term was found, so the formula returns TRUE; otherwise, FALSE.

Fix 2: TEXTJOIN + ISNUMBER + SEARCH (Excel 2019/365+) #

If you're using a newer Excel version with dynamic array support, you can use a more concise formula:

=ISNUMBER(SEARCH("*"&TEXTJOIN("*|*",TRUE,D$1:D$7000)&"*",A1))

How it works: #

• TEXTJOIN("*|*",TRUE,D$1:D$7000): Joins all vocabulary terms with *|* (e.g., "algebra*|*biology*|*history"). The asterisks act as wildcards to match partial text.
• Wrapping the joined string in * ensures we can find the term anywhere in A1.
• ISNUMBER(SEARCH(...)): Returns TRUE if any term is found in A1, FALSE otherwise.

⚠️ Note: This has a character limit—if your combined vocabulary terms exceed 32,767 characters, use the SUMPRODUCT method instead.

Fix 3: COUNTIF (For Exact Whole-Word Matches) #

If your course vocabulary are full words (not substrings—e.g., you don't want "mat" matching "math"), use this array formula (press Ctrl+Shift+Enter in older Excel versions; newer versions handle it automatically):

=COUNTIF(A1,"*"&D$1:D$7000&"*")>0

This checks if any vocabulary term appears as a full word in A1 and returns TRUE or FALSE.

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