模糊查找向来都是一个比较难的点,即使你对VLOOKUP、LOOKUP函数很熟悉,但有些问题也是要想很久。但当你学会下面这5个方法时,工作简直跟开了挂一样!

下图中,我们想要通过E列的姓名在B:C表数据区域中找到对应的销售的产品,因为一款产品会有多个人销售,所以把销售相同产品的姓名放在了同一个单元格中,这种就需要用到模糊查找了。

excel如何模糊查找片段(Excel模糊查找学会这5个方法)(1)

下面跟大家讲解模糊查找的5个方法:

一、使用VLOOKUP函数。

F2单元格的公式:=VLOOKUP("*"&E2&"*",B:C,2,0)

excel如何模糊查找片段(Excel模糊查找学会这5个方法)(2)

公式解析:

给E2单元格前后都加上星号(*:表示通配符),以E2单元格作为查找值,但查找区域中并不完全等于E2,有可能还包含其他字符,所以就需要加上这个通配符。

二、使用LOOKUP FIND函数。

F2单元格的公式:=LOOKUP(,-FIND(E2,$B$2:$B$4),$C$2:$C$4)。

excel如何模糊查找片段(Excel模糊查找学会这5个方法)(3)

公式解析:

FIND(E2,$B$2:$B$4):返回E2单元格的姓名在B2:B4单元格中的起始位置,结果为{#VALUE!;5;#VALUE!},在该数组前面加上符号,得到结果{#VALUE!;-5;#VALUE!}。

=LOOKUP(,{#VALUE!;-5;#VALUE!},$C$2:$C$4):在{#VALUE!;-5;#VALUE!}查找最接近于0的值,显然只有-5,-5所在的位置为2,C2:C4单元格第2行的结果为“纸巾”。

三、使用INDEX MATCH FIND函数。

F2单元格的公式:{=INDEX($C$2:$C$4,MATCH(1,-FIND(E2,$B$2:$B$4)))}。

注意:公式左右两边的大花括号是按“Ctrl Shift Enter”自动生成的,手动输入无效。

excel如何模糊查找片段(Excel模糊查找学会这5个方法)(4)

公式解析:

FIND(E2,$B$2:$B$4):返回E2单元格的姓名在B2:B4单元格中的起始位置,结果为{#VALUE!;5;#VALUE!},在该数组前面加上符号,得到结果{#VALUE!;-5;#VALUE!}。MATCH(1,{#VALUE!;-5;#VALUE!}):MATCH函数用于返回指定数值在指定数组区域中的位置。第1个参数为1时,表示MATCH函数会查找小于或等于{#VALUE!;-5;#VALUE!}这个数组的最大值,也就是-5,-5在该数组中的位置为2,所以公式=MATCH(1,{#VALUE!;-5;#VALUE!})结果为2。=INDEX($C$2:$C$4,2):INDEX函数是返回表或区域中的值或值的引用。该公式的意思是:从C2:C4单元格区域中,返回第2行的值,所以结果为“纸巾”。

四、使用INDEX MIN ISNUMBER FIND ROW函数。

F2单元格的公式:{=INDEX(C:C,MIN(IF(ISNUMBER(FIND(E2,$B$2:$B$4)),ROW($A$2:$A$4),9999)))}

注意:公式左右两边的大花括号是按“Ctrl Shift Enter”自动生成的,手动输入无效。

excel如何模糊查找片段(Excel模糊查找学会这5个方法)(5)

公式解析:

FIND(E2,$B$2:$B$4):返回E2单元格的姓名在B2:B4单元格中的起始位置,结果为{#VALUE!;5;#VALUE!}。ISNUMBER函数检测一个值是否为数值,返回TRUE和FALSE。所以公式ISNUMBER(FIND(E2,$B$2:$B$4))返回{FALSE;TRUE;FALSE},用IF函数判断条件ISNUMBER(FIND(E2,$B$2:$B$4))是否为TRUE,为TRUE时返回A2:A4的行号,为FALSE时返回一个比A2:A4行号大的值,这里返回的结果是{9999;3;9999},再用MIN函数取里面的最小值,为3。=INDEX(C:C,3)的意思是,从C列中,返回第3行的值,结果为“纸巾”。

五、使用INDEX MIN IF ISERROR FIND ROW函数。

F2单元格的公式:{=INDEX(C:C,MIN(IF(ISERROR(FIND(E2,$B$2:$B$4,1)),9999,ROW($2:$4))))}。

注意:公式左右两边的大花括号是按“Ctrl Shift Enter”自动生成的,手动输入无效。

excel如何模糊查找片段(Excel模糊查找学会这5个方法)(6)

公式解析:

FIND(E2,$B$2:$B$4,1):从单元格第1个字符开始查找关键字,返回E2单元格的姓名在B2:B4单元格中的起始位置,结果为{#VALUE!;5;#VALUE!}。用ISERROR判断是否有错误值,返回TRUE和FALSE,ISERROR(FIND(E2,$B$2:$B$4,1))的结果为{TRUE;FALSE;TRUE},用IF函数判断条件ISERROR(FIND(E2,$B$2:$B$4,1))是否为TRUE,如果为TRUE,返回9999,如果为FALSE,返回第2-4行的行号。所以公式IF(ISERROR(FIND(E2,$B$2:$B$4,1)),9999,ROW($2:$4))返回的结果为{9999;3;9999},再用MIN函数取里面的最小值,为3。=INDEX(C:C,3)的意思是,从C列中,返回第3行的值,结果为“纸巾”。

觉得文章不错,请转发和点赞,谢谢!

,