Google Sheets通配符匹配职位描述自动填充薪资方案问询

需求说明 #

有两个Google Sheets表格区域，需要找到表格1（A:B列）中首个包含通配符的单元格（A列），使其匹配表格2（D列）的职位描述值，然后将匹配单元格对应的B列薪资填入表格2的E列对应行。

示例 #

示例数据 #

Job Wildcards,Salary,,Job Description,Salary
*Dev*,40000,,Junior Developer,40000
*Engineer*,50000,,Senior Developer,40000
*Manager*,60000,,Platform Engineer,50000
,,,Project Manager,60000
,,,Team Manager,60000

E列需要自动填充：查找A列中首个匹配D列职位描述的通配符规则，将对应B列的薪资填入，示例中E列已手动填入正确结果。

已尝试方案 #

• 使用VLOOKUP：

  =VLOOKUP(D2,A$2:$B5,2,FALSE)
  

  报错：Did not find value 'Junior Developer' in VLOOKUP evaluation.，原因是VLOOKUP为正向查找逻辑，无法实现通配符反向匹配。

• 使用INDEX+MATCH组合：

  =INDEX(B$2:B$6,MATCH(D2,A$2:A$7,0))
  

  返回#N/A，报错逻辑与VLOOKUP一致，正向匹配不符合需求。

• 使用REGEXMATCH尝试匹配：

  =INDEX(FILTER(REGEXMATCH(A2,ARRAYFORMULA(D2:D7)), NOT(FALSE)), 1)
  

  报错：FILTER has mismatched range sizes. Expected row count: 7. column count: 1. Actual row count: 1, column count: 1.，因范围大小不匹配失败。

• 使用QUERY搭配%通配符：

  =QUERY($A$2:$D$6,"SELECT B where D like A")
  

  未达到预期效果，逻辑不符合反向匹配需求。

解决方案 #

单个单元格公式（适用于E2） #

=INDEX(B$2:B$4, MATCH(TRUE, REGEXMATCH(D2, SUBSTITUTE(A$2:A$4, "*", ".*")), 0))

• 逻辑说明：
  1. SUBSTITUTE(A$2:A$4, "*", ".*")：将A列的通配符*转换为正则表达式中的任意匹配符.*；
  2. REGEXMATCH(D2, ...)：检查D2的职位描述是否匹配A列的每个规则，返回一组布尔值；
  3. MATCH(TRUE, ..., 0)：定位第一个匹配（TRUE）的位置；
  4. INDEX(B$2:B$4, ...)：根据匹配位置返回对应B列的薪资。

数组公式（一次性填充所有E列） #

若需要批量处理D列所有行，可使用数组公式自动填充：

=ARRAYFORMULA(IF(D2:D="", "", INDEX(B$2:B$4, MATCH(TRUE, REGEXMATCH(D2:D, SUBSTITUTE(A$2:A$4, "*", ".*")), 0))))

• 逻辑说明：ARRAYFORMULA批量处理D列所有非空行，自动返回对应E列的薪资结果。

内容的提问来源于stack exchange，提问作者Y. Shallow