Excel自动匹配右侧名称至底部列表的公式实现与解释求助

一、需求实现步骤 #

1. 先确认两个关键区域：
   • 短名区域：周一至周五存放Nick这类简称的单元格（比如你的F3:F7）
   • 全名对照区域：右侧存放NICK-GL这类完整名称的列表（比如你的对应列数据）
2. 把调整好的公式输入到底部要堆叠结果的起始单元格（比如F21），Excel365/2021会自动向下溢出填充结果
3. 每周更新右侧全名列表后，公式会自动同步匹配新的名称

二、找到的公式解析（已适配中文标注） #

先把原公式改成更易懂的中文变量版本：

=LET(
    短名区域, E74:E83,  // 替换成你的周一至周五短名单元格范围，比如F3:F7
    全名区域, T74:T82,  // 替换成右侧完整名称的对照区域，比如K3:K10
    短名分隔符, "/",    // 短名里的分隔符（没有就填""）
    全名分隔符, "-",    // 全名里的分隔符，比如NICK-GL里的"-"
    过滤后短名, FILTER(短名区域, LEN(短名区域), ""),
    IFNA(
        XLOOKUP(
            TEXTBEFORE(过滤后短名, 短名分隔符),
            TEXTBEFORE(全名区域, 全名分隔符),
            全名区域
        ),
        过滤后短名
    )
)

逐段解释各部分作用：

• LET：给公式里的区域和符号起别名，方便修改和阅读，不用重复写长单元格地址
• 短名区域/全名区域：分别对应你的短名输入区和完整名称对照区，直接替换成自己表格里的实际范围就行
• 短名分隔符/全名分隔符：用来拆分名称的符号，比如如果你的全名是NICK-GL，就用"-"拆分出前面的NICK去匹配短名Nick
• 过滤后短名：用FILTER自动去掉短名区域里的空单元格，只处理有内容的简称
• XLOOKUP：核心匹配函数，提取短名（或短名分隔符前的内容），去匹配全名里分隔符前的内容，匹配成功就返回对应的完整名称
• IFNA：如果找不到匹配的全名，就返回原来的短名，避免单元格显示错误值

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