查找多个范围之间不匹配的值

Question

我想看看是否有一种仅使用公式（不使用 VBA）的方法来查找不在范围内的常量列表中的值；并且可以针对不同长度的列表和不同的列数进行动态调整。例如：表 1 是 3x2，表 2 是 4x9，表 3 是 6x8。常量列表的长度始终与它们所属的表的长度相同。

常量列表 {“A”、“B”、“C”、“D”、“E”、“F”、“G”、“H”、“I”、“J”}。

Column 1  Column 2  Column 3
A         J
C         B
G         E
I
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*

我正在寻找一个公式，可以在第 3 列中列出第 1-2 列中没有的常量列表中的值。在此示例中，第 3 列如下所示：

Column 3
D
F
H
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*
*Blank Row*

Answer 1

公式中放入常量列表 {“A”、“B”、“C”、“D”、“E”、“F”、“G”、“H”、“I”、“J”}，

和，

在“第 3 列” D2，公式复制下来：

=IFERROR(INDEX({"A";"B";"C";"D";"E";"F";"G";"H";"I";"J"},SMALL(IF(COUNTIF($A$2:$B$5,{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J"}),4^8,ROW($1:$10)),ROWS($1:1))),"")

Answer 2

这就是逆向思维，而我们一般的做法是如果在指定的条件范围内就返回一个值。

无论如何，为了满足您的需求，我建议您添加一个辅助列，即我的测试中的“测试”。

然后该列使用以下公式：

=COUNTIF($B$2:$C$11,A2)

确认常数A是否出现在A列:B列的条件区域中。如果出现，则返回1

接下来我们需要做的是在第 3 列中使用以下公式：

=LOOKUP(0,D2,A2:A11)

它将返回“范围”列对应的值“0”。