我想看看是否有一种仅使用公式（不使用 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*
答案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))),"")
答案2
这就是逆向思维，而我们一般的做法是如果在指定的条件范围内就返回一个值。
无论如何，为了满足您的需求，我建议您添加一个辅助列，即我的测试中的“测试”。
然后该列使用以下公式：
=COUNTIF($B$2:$C$11,A2)
确认常数A是否出现在A列:B列的条件区域中。如果出现，则返回1
接下来我们需要做的是在第 3 列中使用以下公式：
=LOOKUP(0,D2,A2:A11)
它将返回“范围”列对应的值“0”。