Excel表格模糊匹配搜索改造：实现不区分大小写的部分字符串匹配

直接替换原公式为以下内容：

=IFS(
    C3="DATA", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!A:A)," ",""))),
    C3="MAIL", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!B:B)," ",""))),
    C3="TELEFONO", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!C:C)," ",""))),
    C3="NOME", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!D:D)," ","")))
)

关键改动说明 #

• 动态范围适配：将原公式固定行号的A2:F29改为A:F，自动覆盖DATABASE页所有非空行，无需每日手动调整行范围。
• 忽略空格干扰：通过SUBSTITUTE(xxx," ","")移除搜索关键词和目标单元格中的所有空格，确保Mark B.与markb这类带空格/无空格的文本能匹配。
• 消除大小写差异：用LOWER()将关键词和目标文本统一转为小写，彻底避免大小写对匹配结果的影响。
• 实现部分匹配：ISNUMBER(SEARCH(...))用于判断处理后的关键词是否是处理后目标文本的子串，满足输入mark匹配Mark B.、Markus等场景的需求。

可选优化：空搜索返回全量数据 #

如果需要在搜索框为空时自动显示所有数据，可在IFS最前端添加空值判断条件，完整公式如下：

=IFS(
    RICERCA!D3="", DATABASE!A:F,
    C3="DATA", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!A:A)," ",""))),
    C3="MAIL", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!B:B)," ",""))),
    C3="TELEFONO", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!C:C)," ",""))),
    C3="NOME", FILTER(DATABASE!A:F, ISNUMBER(SEARCH(SUBSTITUTE(LOWER(RICERCA!D3)," ",""), SUBSTITUTE(LOWER(DATABASE!D:D)," ","")))
)

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