Excel中LET/FILTER公式未返回全部匹配值问题排查

第一个公式的核心问题：最后一行计算错误 #

COUNTA(Master!L:L)的逻辑是统计L列所有非空单元格的数量，但它无法正确定位最后一个非空行：

• 如果L列存在中间空行、前面有大量空单元格的情况，COUNTA返回的是总非空数，而非最后一行的行号。比如L列前1000行是空，1001-1327行有数据，COUNTA返回327，此时INDEX(Master!L:L,_LastRow)会指向第327行，直接漏掉了1001-1327的有效数据，导致筛选范围不完整，自然少返回结果。

第二个公式的核心问题：语法错误+逻辑混乱 #

1. 语法错误：公式里写了_FILTER，但没有定义这个名称，应该是FILTER函数；同时IF(_ParentRow="Parent Row",_Hstack,(_CriteriaRange1=A22)*(_CriteriaRange2=B22),"")违反了IF函数的语法（IF只能有3个参数：条件、真值、假值），多写了一个参数，导致公式逻辑失效。
2. 逻辑错误：你想要的是包含父行+符合A22/B22条件的行，但当前IF的写法把筛选条件和返回值混在了一起，没有正确构建筛选逻辑。

修复后的正确公式 #

假设你的需求是：同时显示标记为"Parent Row"的行，以及符合A22（L列匹配）、B22（M列匹配）的行，可以用以下公式：

=IFERROR(LET(
 _LastRow, XLOOKUP(TRUE, Master!L:L<>"", ROW(Master!L:L),,0,-1),
 _FRng, Master!B3:INDEX(Master!B:B,_LastRow),
 _Comments, Master!D3:INDEX(Master!D:D,_LastRow),
 _CriteriaRange1, Master!L3:INDEX(Master!L:L,_LastRow),
 _CriteriaRange2, Master!M3:INDEX(Master!M:M,_LastRow),
 _ParentRow, Master!Q3:INDEX(Master!Q:Q,_LastRow),
 _Hstack, HSTACK(_FRng, _Comments, _CriteriaRange1, _CriteriaRange2),
 FILTER(_Hstack, (_ParentRow="Parent Row") + (_CriteriaRange1=A22)*(_CriteriaRange2=B22))
), "")

关键优化点： #

• 用XLOOKUP(TRUE, Master!L:L<>"", ROW(Master!L:L),,0,-1)精准定位L列最后一个非空行的行号，避免COUNTA或MATCH的潜在问题。
• 用HSTACK替代CHOOSE({1,2,3},...)，更直观地合并多列数据（原CHOOSE里写了{1,2,3}但合并了4列，也是一处小疏漏）。
• 筛选条件用+表示“或”逻辑，*表示“与”逻辑，正确实现「父行 或 符合双条件」的筛选需求。
• 修正了原公式中的语法错误，确保函数调用正确。

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