Excel数组公式需求：提取字符串数字并匹配返回指定内容

完全可以用ARRAYFORMULA实现需求，以下是具体公式和拆解说明：

核心公式（带错误处理） #

假设Sheet B的源数据在A列，公式写在B列第一行（比如B2）：

=ARRAYFORMULA(IF(A2:A="", "", 
  IFERROR(
    VLOOKUP(
      REGEXEXTRACT(A2:A, "#(\d+)"), 
      {REGEXEXTRACT(Sheet1!A2:A, "#(\d+)"), TRIM(LEFT(Sheet1!A2:A, FIND("#", Sheet1!A2:A)-1))}, 
      2, 
      FALSE
    ), 
    "无匹配"
  )
))

公式拆解 #

• REGEXEXTRACT(A2:A, "#(\d+)")：从Sheet B的单元格中提取#后的纯数字（比如从J Doe #23 (other info no numbers)中提取23）
• {REGEXEXTRACT(Sheet1!A2:A, "#(\d+)"), TRIM(LEFT(Sheet1!A2:A, FIND("#", Sheet1!A2:A)-1))}：构建虚拟匹配数组
  • 第一列：提取Sheet1中每个单元格的数字作为匹配键
  • 第二列：提取Sheet1中#左侧的内容，并用TRIM清除前后空格（比如从John D #23得到John D）
• VLOOKUP(...)：用Sheet B提取的数字匹配虚拟数组，返回对应的名称内容
• ARRAYFORMULA：让公式自动批量应用到A列所有非空单元格，无需手动下拉
• IF(A2:A="", "", ...)：跳过空单元格，避免无效计算
• IFERROR(..., "无匹配")：处理格式错误或无匹配的情况，返回自定义提示

注意事项 #

• 确保Sheet1和Sheet B的单元格都包含#+数字的格式，否则公式会触发错误（已用IFERROR处理）
• 如果数字可能包含多位数，公式依然有效，\d+会匹配连续的所有数字

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