Excel有序比对两列并跳过空白单元格的公式需求

需求：在Excel中设置公式，按顺序比对A、B两列内容，跳过B列中的空白单元格，对应C列返回指定结果。

样本数据集 #

注：原样本第4行A列值疑似输入错误，已修正为5以匹配预期的match结果。

尝试过的无效公式 #

• 公式1：

=IF(B1="", "", IF(ISNUMBER(MATCH(A1, IF(B:B<>"", B:B), 0)), "Match", "Difference"))

• 公式2：

=IF(AND(A1<>"", B1<>"", A1=B1), "Match", IF(B1="", "", "Difference"))

• 公式3：

=IF(A1<>"", IFERROR(IF(ISBLANK(B1), IFERROR(INDEX(B:B, MATCH(FALSE, ISBLANK(B:B), 0)), ""), IF(A1=B1, "Match", "Difference")), ""), "")

正确公式解决方案 #

方案1：适用于Excel 365/2021（动态数组） #

在C1单元格输入以下公式，回车后自动填充整列：

=LET(
    B_non_blank, FILTER(B:B, B:B<>""),
    B_count, SCAN(0, B:B, LAMBDA(a,b, IF(b<>"", a+1, a))),
    A_count, SCAN(0, A:A, LAMBDA(a,b, IF(b<>"", a+1, a))),
    base_result, IF(B:B="", "skipped", IF(INDEX(B_non_blank, A_count)=A:A, "match", "difference")),
    IF(AND(A:A="", B:B<>""), IF(INDEX(B_non_blank, MAX(A_count))=B:B, "match", "difference"), base_result)
)

方案2：适用于旧版Excel（非动态数组） #

在C1单元格输入以下数组公式，按Ctrl+Shift+Enter确认后下拉填充：

=IF(B1="","skipped",IF(A1=INDEX(B:B,SMALL(IF(B:B<>"",ROW(B:B)),COUNTIF(B$1:B1,"<>"""))),"match","difference"))

逻辑说明：

1. 优先判断B列当前行是否为空白，是则返回skipped
2. 针对B列非空白的行，计算当前行及以上B列非空白单元格的数量，以此为索引提取B列对应位置的非空白值
3. 将该值与当前行A列值比对，相等返回match，不等返回difference
4. 针对A列空白但B列非空白的情况（如样本最后一行），自动匹配A列最后一个非空白值与当前B列值。

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