我在知乎发现有人提了一个问题：如何批量从公司名称中提取城市名（https://www.zhihu.com/question/283581621）。

如图所示，如果A列中的公司名称包含D列中的某个城市，就在B列对应的单元格中显示城市名。例如A1中的“北京市东湖五金厂”包含D列中的“北京”，就在B1单元格中显示对应的城市。

有位大神给了如下答案（https://www.zhihu.com/question/283581621/answer/432248743）：

在B1单元格中输入下列公式：=LOOKUP(4^8,FIND(D\$1:D\$3,A1),D\$1:D\$3)。再拖动填充到B2~B4单元格。

但是这位大神没有作出解析，我在此解析一下。

解析：

因为这道题把城市的名称保存在同一列中，我们容易联想起LOOKUP函数的向量形式，它可以在一个向量中寻找某个值，返回另一个向量中处于同一位置的值。

“

LOOKUP函数的向量形式的语法为：

LOOKUP(lookup_value, lookup_vector, [result_vector])

  • lookup_value 必需。 LOOKUP 在第一个向量中搜索的值。 Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。

  • lookup_vector 必需。 只包含一行或一列的区域。 lookup_vector 中的值可以是文本、数字或逻辑值。

    重要: lookup_vector 中的值必须按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否则，LOOKUP 可能无法返回正确的值。 文本不区分大小写。

  • result_vector 可选。只包含一行或一列的区域。result_vector 参数必须与 lookup_vector 参数大小相同。其大小必须相同。

”

很显然，D\$1:D\$3，也就是向量 {广州,上海,北京}，构成了第三个参数result_vector。我们需要构建第二个参数lookup_vector。

这道题要求从公司名称中提取地名，例如从“北京市东湖五金厂”中提取“北京”，我们容易联想起FIND函数。它可以在一个字符串中查找另一个字符串，并且返回后者在前者中的位置，如果找不到，会返回#VALUE!错误。

“

FIND函数的语法为：

FIND(find_text, within_text, [start_num])

  • find_text 必需。 要查找的文本。

  • within_text 必需。 包含要查找文本的文本。

  • start_num 可选。 指定开始进行查找的字符。 within_text 中的首字符是编号为 1 的字符。 如果省略 start_num，则假定其值为 1。

”

FIND函数的第一个参数find_text，可以是向量或数组。我们可以用FIND函数，依次在A1单元格中查找“广州”、“上海”、“北京”的位置，即FIND(D\$1:D\$3,A1)。很明显，FIND函数在这里找不到“广州”或“上海”，但是可以找到“北京”并返回其位置1。于是，查找结果会临时存储为一个向量 {#VALUE!,#VALUE!,1}。这个向量可以充当LOOKUP函数的第二个参数lookup_vector。

现在，我们把目光放到LOOKUP函数的第一个参数lookup_value。

  • 这个函数有一个属性：如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配。

  • 这个函数还有一个隐藏属性：它会忽略第二个参数lookup_vector中的错误信息，所以对于#VALUE!这样的错误信息，它只会视而不见。

我们要在此基础上考虑容错性的问题。假如A1单元格不是“北京市东湖五金厂”，而是“中国联合通信集团北京分公司”，也就是说，“北京”的位置不一定是1。穷举一下，由于在Excel中，每个单元格最多可以输入32767个字符，而且“北京”可能在任意一个位置，那么“北京”的位置将会是1~32766。

因为“北京”的位置可能是1~32766之间的任意整数，为了能让LOOKUP找到“北京”所在的位置，我们必须把第一个参数lookup_value设为超过32766的一个整数，32767正好合适。

考虑到早期版本的Excel可能把一个汉字计为2个字符，不妨把32767翻一倍，就是65534。4^8=65536当然也可以。

综上所述，就得到了答案=LOOKUP(4^8,FIND(D\$1:D\$3,A1),D\$1:D\$3)。

参考资料：

[1] LOOKUP函数。https://support.office.com/zh-cn/article/LOOKUP-函数-446D94AF-663B-451D-8251-369D5E3864CB

[2] FIND、FINDB函数。https://support.office.com/zh-cn/article/FIND、FINDB-函数-C7912941-AF2A-4BDF-A553-D0D89B0A0628

推荐阅读：

写给MOS认证考生：如何让Outlook 2016关联Outlook和QQ邮箱？

写给MOS认证考生：如何单独安装Outlook或Access？

你凭什么让 HR 相信你精通 Office ？

↓↓↓↓想成为Office高手吗？请猛击左下角“阅读原文”报名参加MOS认证考试，考完就能成为高手！