Excel实现列表精确匹配查找而非返回首个匹配的公式写法

当前使用如下公式在指定列表中执行查找：

=IFERROR(INDEX(PRODUCT_TALL[Product],MATCH(TRUE,(ISNUMBER(SEARCH(PRODUCT_TALL[Product],K36,1))),0),1),"")

该公式仅返回查找到的首个匹配结果，示例场景：

• 待查找列表值：

Ator
Atoreza
Atorvastatin

• 当前公式返回效果：
  
  实际需要返回匹配精度最高的Atorvastatin，需调整公式实现该需求。

原公式逻辑为判断产品列的文本是否包含在K36单元格内容中，命中即标记为匹配，MATCH默认按列表原有顺序返回第一个匹配项，因此排序靠前的短文本Ator会被优先返回。要返回最长、最精确的匹配项，根据使用的Excel版本选择对应公式即可：

Excel 365/2021及以上（支持动态数组） #

直接输入以下公式按回车生效，无需组合键：

=TAKE(SORT(FILTER(PRODUCT_TALL[Product],ISNUMBER(SEARCH(PRODUCT_TALL[Product],K36))),LEN(PRODUCT_TALL[Product]),-1),1)

公式逻辑：先筛选出所有在K36中存在的匹配项，按文本字符长度降序排序后取第一个值，无匹配时自动返回空值，无需额外嵌套IFERROR。
如果需要兼容排序逻辑，也可以使用简化写法：

=IFERROR(INDEX(SORT(PRODUCT_TALL[Product],LEN(PRODUCT_TALL[Product]),-1),MATCH(TRUE,ISNUMBER(SEARCH(SORT(PRODUCT_TALL[Product],LEN(PRODUCT_TALL[Product]),-1),K36)),0)),"")

Excel 2019及更早旧版本 #

输入以下公式后，按Ctrl+Shift+Enter三键确认数组公式：

=IFERROR(INDEX(PRODUCT_TALL[Product],MATCH(MAX(IF(ISNUMBER(SEARCH(PRODUCT_TALL[Product],K36)),LEN(PRODUCT_TALL[Product]),0)),LEN(PRODUCT_TALL[Product])*ISNUMBER(SEARCH(PRODUCT_TALL[Product],K36)),0)),"")

公式逻辑：先计算所有匹配项的字符长度，取出最长的长度值，再匹配对应长度的产品文本返回，即可得到最精确的匹配结果。

补充：如果需求是K36内容与产品列值完全相等才算匹配，直接将公式中SEARCH(PRODUCT_TALL[Product],K36)替换为PRODUCT_TALL[Product]=K36（不区分大小写）或EXACT(PRODUCT_TALL[Product],K36)（区分大小写）即可，无需做长度排序。

内容的提问来源于stack exchange，提问作者محمد كمال زاخر