Excel技巧求助：若单元格包含数组值，提取该值

Got it, let's tackle this. You need to pull the matching category from C2:C5 that exists as a substring in A2's text, right? Here are two solid solutions depending on your Excel version:

For Modern Excel (365/2021+) #

Use XLOOKUP with a dynamic array check—it's clean and straightforward:

=XLOOKUP(TRUE,ISNUMBER(SEARCH($C$2:$C$5,A2)),$C$2:$C$5,"")

Breakdown: #

• SEARCH($C$2:$C$5,A2): Checks each category in C2:C5 to see if it's present in A2's text, returns a position number if found, #VALUE! if not.
• ISNUMBER(...): Converts those results to TRUE (if a match was found) or FALSE (no match).
• XLOOKUP finds the first TRUE in that array, grabs the corresponding category from C2:C5, and returns "" if no matches exist.

For Older Excel Versions (Pre-365/2021) #

If you don't have XLOOKUP, use an INDEX/MATCH combo with an array formula (you might need to press Ctrl+Shift+Enter instead of just Enter if your version doesn't support dynamic arrays natively):

=INDEX($C$2:$C$5,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$5,A2)),0),"")

Breakdown: #

• Same SEARCH/ISNUMBER logic to create a TRUE/FALSE array of matches.
• MATCH finds the position of the first TRUE in that array.
• INDEX pulls the category from C2:C5 at that position, and the final "" ensures empty text if no match is found.

Important Notes: #

• Use absolute references ($C$2:$C$5) so the category range doesn't shift if you drag the formula down to other rows.
• SEARCH is case-insensitive—if you need case-sensitive matching, swap it out for FIND instead.

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