您可以为Excel中的= if(isnumber(search)))公式做多个选择吗? [英] Can you do multiple options for the =if(isnumber(search))) formula in excel?

问题描述

此公式在单元格I3中查找字符串"BFA".如果找到，则返回"BFA"；否则，返回"BFA".如果不是，则返回否".

This formula looks for the string "BFA" inside cell I3. If found, it returns "BFA"; if not, it returns "No".

=IF(ISNUMBER(SEARCH("BFA",I3)),"BFA","No")

如何修改它以同时适用于多个字符串?例如，如果找到{"BFA"，"MFA"，"BA"，"MA"}中的任何一个，则返回找到的内容；如果不是，则返回否".

How can I modify this to work for multiple strings at the same time? For example, if any of {"BFA", "MFA", "BA", "MA"} is found, then return what was found; if not, return "No".

推荐答案

您可以将此版本与LOOKUP一起使用:

You can use this version with LOOKUP:

=IFERROR(LOOKUP(2^15,SEARCH({"BFA","MFA","BA","MA"},I3),{"BFA","MFA","BA","MA"}),"No")

SEARCH函数返回一个数字或错误数组(取决于是否找到每个字符串).当您在该数组中查找2^15时，匹配始终与 last 数字匹配，然后LOOKUP返回相应的文本字符串.如果没有匹配项，则会得到#N/A，而IFERROR会将其转换为否".

The SEARCH function returns an array of either numbers or errors (depending on whether each string is found or not). When you lookup 2^15 in that array, the match is always with the last number, and then LOOKUP returns the corresponding text string. If there are no matches, you get #N/A and IFERROR converts that to "No".

例如，如果您有同时包含"BFA"和"BA"的单元格，则该公式将返回列表中的最后一个单元格，即"BA".

So if you have any cells that contain both "BFA" and "BA", for example, the formula will return the last one in your list, i.e. "BA".

这篇关于您可以为Excel中的= if(isnumber(search)))公式做多个选择吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！