如何用Index/Match/Lookup提取Excel表格非空文本单元格列表（禁用VBA）

适用场景 #

支持所有Excel版本（含无动态数组的旧版本），可适配任意尺寸的原始表格，无需VBA或透视表。

前提假设 #

原始数据范围定义（可根据实际表格调整）：

• 检查项目列：$A$2:$A$[总行数]（A列存储Procedure，从第2行开始）
• 就诊次数表头：$B$1:$[最后列]$1（第1行存储Visit 1~N）
• 标记区域：$B$2:$[最后列]$[总行数]（B到最后列、第2行到最后行存储X/Y/Z标记）

提取单个组合（比如Arm + X）的操作步骤 #

1. 在目标区域输入标题，例如Arm (X)。
2. 在标题下方的第一个单元格（比如G2）输入以下数组公式：

   =IFERROR(INDEX($B$1:$E$1, SMALL(IF(($A$2:$A$5="Arm")*($B$2:$E$5="X"), COLUMN($B$2:$E$5)-COLUMN($B$2)+1), ROWS($G$2:G2))), "")
   

   • 旧版Excel输入后需按Ctrl+Shift+Enter确认；Excel 365/2021直接回车即可。
3. 下拉公式直到出现空白单元格，即可得到所有匹配的就诊次数。

公式拆解 #

• ($A$2:$A$5="Arm")*($B$2:$E$5="X")：生成判断数组，同时满足检查项目为Arm、标记为X的位置返回1，其余返回0。
• COLUMN($B$2:$E$5)-COLUMN($B$2)+1：将匹配位置的列号转换为相对就诊表头的偏移量（比如B列对应1、C列对应2）。
• SMALL(..., ROWS($G$2:G2))：依次提取第1、2、3...个匹配的偏移量，下拉时自动递增序号。
• INDEX($B$1:$E$1, ...)：根据偏移量提取对应的就诊次数表头文本。
• IFERROR(..., "")：当无更多匹配项时返回空白，避免显示错误值。

适配不同尺寸表格的调整方法 #

1. 扩展数据范围：把公式中的$A$2:$A$5、$B$1:$E$1、$B$2:$E$5替换为你实际的表格范围（比如$A$2:$A$100、$B$1:$Z$1、$B$2:$Z$100，覆盖所有可能的行和列）。
2. 动态切换筛选条件：将公式中的"Arm"和"X"替换为单元格引用（比如$I$1存储检查项目，$I$2存储标记），只需修改这两个单元格内容就能快速切换筛选组合。

示例效果 #

针对你的原始数据，使用公式后：

• 筛选Arm(X)会得到Visit 2、Visit 4
• 筛选Eye(Z)会得到Visit 4

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