Excel如何从主列表中排除指定禁入列表条目生成过滤新列表

你写的=IF(List1=List2,"",List1)是按单元格行位置逐行对位匹配：只有主列表某行的内容，和禁入列表同一行的内容完全相等时才会被置空，根本不会校验当前条目是否存在于禁入列表的其他行，自然会漏掉位置不对的禁入条目（比如你提到的ABCD）。

根据你使用的Excel版本选择对应公式即可：

• 365/2021及以上支持动态数组的版本，直接在新列表起始单元格输入以下公式，会自动溢出返回全部过滤后的结果，无需手动拖拽：

  =FILTER(List1,ISNA(MATCH(List1,List2,0)))
  

  逻辑说明：用MATCH精确匹配检查主列表每个条目是否在禁入列表中存在，匹配不到（返回#N/A错误，ISNA判定为真）的条目就保留，存在的就过滤。也可以用COUNTIF版本，效果完全一致：

  =FILTER(List1,COUNTIF(List2,List1)=0)
  

• 2019及更早不支持动态数组的旧版本，在新列表第一个单元格输入以下数组公式，输入完成后按Ctrl+Shift+Enter三键确认，再向下拖拽填充直到出现空白单元格即可：

  =IFERROR(INDEX(List1,SMALL(IF(ISNA(MATCH(List1,List2,0)),ROW(INDIRECT("1:"&ROWS(List1)))),ROW(A1))),"")
  

• 如果两个列表的条目可能存在前后多余空格导致匹配失效，可以把公式里的List1替换为TRIM(List1)，List2替换为TRIM(List2)，做去空格处理后再匹配
• 命名范围不要包含多余的整行/整列空单元格，否则会返回无意义的空值

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