Excel中实现模糊查询-LOOKUP+FIND函数

LOOKUP函数

函数简介

  LOOKUP函数是Excel中的一种运算函数，实质是返回向量或数组中的数值，要求数值必须按升序排序。

------粘百度百科

函数使用方法

（1）向量形式：公式为

=LOOKUP(lookup_value,lookup_vector,result_vector)

式中 lookup_value—函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用；
lookup_vector—只包含一行或一列的区域，其可以是文本、数字或逻辑值；
result_vector—只包含一行或一列的区域，其大小必须与 lookup_vector 相同。
（2）数组形式：公式为

= LOOKUP(lookup_value,array)

式中 array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup_value 进行比较。
例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。
 
注意：
  array和lookup_vector的数据必须按升序排列，否则函数LOOKUP不能返回正确的结果。文本不区分大小写。

• 如果函数LOOKUP找不到lookup_value，则查找array和 lookup_vector中小于lookup_value的最大数值。
• 如果lookup_value小于array和 lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。
• 另外还要注意：函数LOOKUP在查找字符方面是不支持通配符的，但可以使用FIND函数的形式来代替。

------粘百度百科

 

FIND函数

函数简介

  Find函数用来对原始数据中某个字符串进行定位，以确定其位置。Find函数进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。

------粘百度百科

函数语法

该函数的语法规则如下：

=FIND(find_text,within_text,start_num)

Find_text 是要查找的字符串。
Within_text 是包含要查找关键字的单元格。就是说要在这个单元格内查找关键字。
Start_num 指定开始进行查找的字符数。比如Start_num为1，则从单元格内第一个字符开始查找关键字。如果忽略 start_num，则假设其为 1。
 
注意：
  使用find函数可从文本字符串中查找特定的文本，并返回查找文本的起始位置。查找时，要区分大小写。全角和半角字符。查找结果的字符位置不分全角和半角，作为一个字符来计算。可以单独使用find函数，例如。按照查找字符的起始位置分开文本字符串，或替换部分文本字符串等，也多用于处理其他信息。计数单位如果不是字符而是字节时，请使用findb函数。find函数和findb函数具有相同的功能，但它们的计数单位不同。 [2]
  使用 start_num 可跳过指定数目的字符。例如，假定使用文本字符串“AYF0093.YoungMensApparel”，如果要查找文本字符串中说明部分的第一个“Y”的编号，则可将 start_num 设置为 8，这样就不会查找文本的序列号部分。FIND 将从第 8 个字符开始查找，而在下一个字符处即可找到 find_text，于是返回编号 9。FIND 总是从 within_text 的起始处返回字符编号，如果 start_num 大于 1，也会对跳过的字符进行计数。 [3]

• 如果 find_text 是空文本 ()，则 FIND 则会返回数值1。
• Find_text 中不能包含通配符。
• 如果within_text 中没有 find_text，则 FIND返回错误值 #VALUE!。
• 如果 start_num 不大于 0，则FIND返回错误值 #VALUE!。
• 如果 start_num 大于 within_text 的长度，则 FIND 返回错误值#VALUE!。

------粘百度百科

示例

表格字段信息展示如下：

姓名	手机型号	精准匹配	模糊匹配
张1	三星Galaxy S21 Ultra	#N/A
张2	华为 MATE 9	华为 MATE 9
张3	华为 MATE Xs	#N/A
张4	华为 MATE 30 PRO	华为 MATE 30 PRO
张5	三星Note8	#N/A
张6	华为 MATE 30 PRO	华为 MATE 30 PRO
张7	iPhone 13 PRO Max	#N/A
张8	华为 P40	华为 P40
张9	华为 MATE 30 PRO	华为 MATE 30 PRO
张10	iPhone 7 Plus	#N/A
张11	华为 NOVA 2 Plus	华为 NOVA 2 Plus
张12	华为 NOVA 2s	华为 NOVA 2s
张13	华为 NOVA 4e	华为 NOVA 4e
张14	华为 P10 Plus	华为 P10 Plus

  其中精准匹配的语句在上篇文章有详细讲解，这里用的数据也是上篇文章表格信息。

  现在要查看sheet1子表中B列手机型号与sheet3子表中A列手机型号模糊匹配结果：

• 匹配的话，在sheet1子表中C列返回模糊匹配上的手机型号；
• 不匹配的话，在sheet1子表中C列返回#N/A。

  这便使用到了LOOKUP函数和FIND函数，在D2单元格中写==LOOKUP(1,0/FIND(Sheet3!A$1:A$15,B2),Sheet3!A$1:A$15)并按下回车键，对应文本便自动匹配，匹配后信息展示如下所示：

=LOOKUP(1,0/FIND(Sheet3!A$1:A$15,B2),Sheet3!A$1:A$15)
//其中Sheet3!A$1:A$15是对应要匹配查找的字符串，该字符串要升序排序
//其中B2是当前sheet1子表中要查找关键字的单元格

姓名	手机型号	精准匹配	模糊匹配
张1	三星Galaxy S21 Ultra	#N/A	#N/A
张2	华为 MATE 9	华为 MATE 9	华为 MATE 9
张3	华为 MATE Xs	#N/A	#N/A
张4	华为 MATE 30 PRO	华为 MATE 30 PRO	华为 MATE 30 PRO
张5	三星Note8	#N/A	#N/A
张6	华为 MATE 30 PRO	华为 MATE 30 PRO	华为 MATE 30 PRO
张7	iPhone 13 PRO Max	#N/A	#N/A
张8	华为 P40	华为 P40	华为 P40
张9	华为 MATE 30 PRO	华为 MATE 30 PRO	华为 MATE 30 PRO
张10	iPhone 7 Plus	#N/A	#N/A
张11	华为 NOVA 2 Plus	华为 NOVA 2 Plus	华为 NOVA 2
张12	华为 NOVA 2s	华为 NOVA 2s	华为 NOVA 2
张13	华为 NOVA 4e	华为 NOVA 4e	华为 NOVA 4
张14	华为 P10 Plus	华为 P10 Plus	华为 P10

  由“精准匹配”和“模糊匹配”两列对比可以看出，模糊查询对比是返回大于自身值的上一个单元格中的字符串。
模糊查询举例：
  sheet3子表中部分展示顺序如下图所示：

  当前姓名为“张11”对应的手机型号为“华为 NOVA 2 Plus”。
1、“华为 NOVA 2 Plus”先与“华为 NOVA 2”比对，“华为 NOVA 2 Plus”比“华为 NOVA 2”大，继续往下顺序对比；
2、“华为 NOVA 2 Plus”和“华为 NOVA 2 PLUS”对比，“华为 NOVA 2 Plus”比“华为 NOVA 2 PLUS”小；
3、所以返回的是比自身值“华为 NOVA 2 Plus”大的“华为 NOVA 2 PLUS”上一个单元格中的字符串“华为 NOVA 2”。

sheet1和sheet3子表具体如下图所示：