技术问询：Excel中匹配文本内关键词并返回对应SKU的公式实现

Got it, let's tackle this problem. The classic INDEX/MATCH with wildcards falls short here because you're checking against a range of keywords instead of a single lookup value—no worries, we've got solid alternatives depending on your Excel version.

Option 1: Excel 365/2021 (Dynamic Array Support) #

This is the cleanest approach, thanks to dynamic array functions that handle range-based checks natively.

Return the first matching SKU #

In cell O2, enter this formula and drag it down:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(G$2:G$100, N2)), A$2:A$100, "No Match")

• SEARCH(G$2:G$100, N2): Checks each keyword in G2:G100 to see if it exists in N2 (case-insensitive; use FIND instead if you need case sensitivity).
• ISNUMBER(...): Converts the search results to TRUE (found) or FALSE (not found).
• XLOOKUP finds the first TRUE in the array and returns the corresponding SKU from A2:A100. Replace "No Match" with whatever you want to show if no keywords are found.

Return all matching SKUs (comma-separated) #

If multiple keywords match and you want all corresponding SKUs, use this instead:

=TEXTJOIN(", ", TRUE, FILTER(A$2:A$100, ISNUMBER(SEARCH(G$2:G$100, N2)), "No Match"))

• FILTER grabs all SKUs where the keyword was found in N2.
• TEXTJOIN combines them into a single string with commas (adjust the ", " separator to your preference).

Option 2: Older Excel Versions (No Dynamic Arrays) #

If you're using Excel 2019 or earlier, you'll need an array formula (enter it with Ctrl+Shift+Enter instead of just Enter):

Return the first matching SKU #

=IFERROR(INDEX(A$2:A$100, MIN(IF(ISNUMBER(SEARCH(G$2:G$100, N2)), ROW(A$2:A$100)-ROW(A$2)+1, 9999))), "No Match")

• IF(ISNUMBER(...), ROW(...)-ROW(A$2)+1, 9999): Returns the relative row number of matching keywords, or a large number (9999) for non-matches.
• MIN(...) picks the first matching row number.
• INDEX pulls the corresponding SKU, and IFERROR handles cases where no matches are found.

Important Notes #

• Replace G$2:G$100 and A$2:A$100 with your actual keyword and SKU ranges (make sure to use absolute references with $ so the range doesn't shift when dragging the formula down).
• If your keyword list is very long, consider converting it to an Excel Table (Ctrl+T) for easier range management—tables automatically expand when you add new keywords.

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