在Google Sheets中使用可变查询条件的查找表匹配取值

核心逻辑 #

匹配规则为：当Input Table某行与Lookup Table某行中所有非空条件列的内容完全一致时，将Lookup Table该行的five列值填入Input Table对应行的five列。以下是适配100列场景的高效实现方法：

方法1：XLOOKUP + 数组拼接匹配（推荐） #

假设Input Table数据范围为Input!A2:ZZ（ZZ可覆盖100列），Lookup Table数据范围为Lookup!A2:ZZ，five列对应E列。在Input Table的E2单元格输入以下公式，下拉或按Ctrl+Enter批量应用：

=BYROW(A2:ZZ, LAMBDA(row, 
  XLOOKUP(
    TEXTJOIN("|", TRUE, FILTER(row, Lookup!A1:ZZ1<>"")), 
    TEXTJOIN("|", TRUE, FILTER(Lookup!A2:ZZ, Lookup!A1:ZZ1<>"")), 
    Lookup!E2:E, 
    ""
  )
))

公式说明 #

• BYROW(A2:ZZ, LAMBDA(row, ...))：遍历Input Table的每一行数据
• TEXTJOIN("|", TRUE, FILTER(row, Lookup!A1:ZZ1<>""))：将当前行中，Lookup Table表头非空的列（即有效条件列）的值用|拼接成唯一匹配标识
• 第二个TEXTJOIN同理处理Lookup Table的每一行，生成匹配基准标识
• XLOOKUP通过对比标识完成匹配，返回Lookup Table对应行的five列值，无匹配时返回空字符串

方法2：QUERY函数动态构造条件 #

同样基于E列填充，公式如下：

=BYROW(A2:ZZ, LAMBDA(row, 
  QUERY(Lookup!A:ZZ, 
    "select E where " & TEXTJOIN(" and ", TRUE, 
      ARRAYFORMULA(IF(Lookup!A1:ZZ1<>"", "Col"&SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1))&"='"&INDEX(row, , SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1)))&"'", ""))
    ) & " limit 1", 
    0
  )
))

公式说明 #

• ARRAYFORMULA(IF(...))：遍历Lookup Table的表头，为每个非空条件列构造ColX='对应值'的查询条件
• TEXTJOIN(" and ", TRUE, ...)：将所有条件用and连接，实现“所有非空条件都匹配”的逻辑
• QUERY执行查询，返回第一个符合条件的five列值，limit 1避免多匹配结果冲突

注意事项 #

1. 表头一致性：确保Input Table和Lookup Table的表头完全对应，否则列匹配会出错
2. 数值类型适配：如果条件列是数值类型，需去掉条件中的单引号，可修改为：

   ARRAYFORMULA(IF(Lookup!A1:ZZ1<>"", IF(ISNUMBER(INDEX(row, , SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1)))), "Col"&SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1))&"="&INDEX(row, , SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1))), "Col"&SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1))&"='"&INDEX(row, , SEQUENCE(1, COLUMNS(Lookup!A1:ZZ1)))&"'"), ""))
   

3. 逻辑切换：若需要“任意非空条件匹配”而非“全部匹配”，将公式中的" and "替换为" or "

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