Google Sheets多行列动态查询公式求助：匹配值为5的对应数据

Google Sheets 解决方案 #

方法1：FLATTEN + QUERY + SPLIT

把横向数据转成纵向结构后筛选匹配项，一步输出结果：

=ARRAYFORMULA(SPLIT(QUERY(FLATTEN(A2:A4&"|"&B1:I1,B2:I4),"SELECT Col1 WHERE Col2=5",0),"|"))

原理：

1. FLATTEN(A2:A4&"|"&B1:I1,B2:I4)：将每一行的A列ID与对应列表头用|拼接，和该列单元格值拆成两行，把整个横向区域转成纵向两列数据。
2. QUERY(..., "SELECT Col1 WHERE Col2=5",0)：筛选出值为5的行，提取拼接好的ID+表头字符串。
3. SPLIT(..., "|")：把字符串拆分为两列，得到最终的ID和对应表头。

方法2：BYCOL + LAMBDA + QUERY

用Lambda函数遍历每一列，针对性提取匹配项：

=ARRAYFORMULA(QUERY({BYCOL(B2:I4, LAMBDA(col, IF(col=5, A2:A4, ""))),BYCOL(B2:I4, LAMBDA(col, IF(col=5, B1:I1, "")))}, "SELECT Col1,Col2 WHERE Col1<>''", 0))

原理：

1. 第一个BYCOL遍历B到I的每一列，单元格值为5时返回对应行的A列ID，否则返回空值。
2. 第二个BYCOL同理，返回对应列的表头。
3. QUERY过滤掉空行，输出有效结果。

Excel 解决方案 #

用LET函数整合逻辑，实现动态匹配：

=LET(
    data_range, A2:I4,
    header_range, B1:I1,
    id_range, A2:A4,
    match_values, FILTER(data_range, data_range=5),
    match_row_nums, XMATCH(match_values, data_range),
    match_col_nums, XMATCH(match_values, TRANSPOSE(data_range)) + 1,
    final_result, CHOOSE({1,2}, INDEX(id_range, match_row_nums), INDEX(header_range, match_col_nums)),
    final_result
)

原理：

1. 先定义各区域变量简化公式。
2. FILTER提取所有值为5的单元格。
3. XMATCH匹配这些单元格的行号和列号。
4. INDEX根据行号列号提取对应ID和表头，CHOOSE组合成两列结果。

原QUERY公式的问题 #

原公式=QUERY(A1:I4,"SELECT A WHERE B=5",0)只能固定检查B列，QUERY的SQL语法无法直接遍历所有列做条件判断，必须先转换数据结构为纵向，或用Lambda类函数实现列的动态遍历。

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