excel 用公式查找单元格

简介

今天要介绍的是，根据查找单元格内容来进行类别匹配，如图所示

MATCH函数

简介

匹配函数,指返回指定数值在指定数组区域中的位置。
用途：
(1)确定列表中某个值的位置；
(2)对某个输入值进行检验，确定这个值是否存在某个列表中；
(3)判断某列表中是否存在重复数据；
(4)定位某一列表中最后一个非空单元格的位置。
查找文本值时，函数 MATCH 不区分大小写字母。

语法：

MATCH(lookup_value, lookup_array, match_type)

参数	简单说明	数据类型	备注
lookup_value	需要在数据表（lookup_array）中查找的值	数值，文本，单元格引用	可以包含通配符，*和？，星号可以匹配任何字符序列；问号可以匹配单个字符
lookup_array	可能包含有所要查找数值的连续的单元格区域	一维数组	区域必须是某一行或某一列
match_type	表示查询的指定方式	-1、0或1，默认为1

• 1：查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列：否则，当遇到比lookup_value更大的值时，即时终止查找并返回此值之前小于或等于lookup_value的数值的位置。如果lookup_array的值均小于或等于lookup_value，则返回数组最后一个值的位置；如果lookup_array的值均大于lookup_value，则返回#N/A。

• 0：查找等于lookup_value的第一个数值，lookup_array按任意顺序排列；

• -1：查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列；否则，当遇到比lookup_value更小的值时，即时终止查找并返回此值之前大于或等于lookup_value的数值的位置。如果lookup_array的值均大于或等于lookup_value，则返回数组最后一个值的位置；如果lookup_array的值均小于lookup_value，则返回#N/A。

使用：

查找E列，内容包含A2单元格的行号

=MATCH("*"&A2&"*",$E:$E,0)

逗号匹配

假设单元格内容以逗号区别项目，为了使相似值不互相影响（类似str和string），因此要进行区别匹配

//only 只有一个项目，例如单元格只有str一个字符
=IF(ISERROR(MATCH(A2,$E:$E,0)),"无",MATCH(A2,$E:$E,0))

//begin 项目在第一个位置，例如单元格内容为str，abc
=IF(ISERROR(MATCH(A2&",*",$E:$E,0)),"无",MATCH(A2&",*",$E:$E,0))

//mid 项目在中间位置，例如单元格内容为a，str，b
=IF(ISERROR(MATCH("*,"&A2&",*",$E:$E,0)),"无",MATCH("*,"&A2&",*",$E:$E,0))

//end 项目在末尾，例如单元格内容为a,str
=IF(ISERROR(MATCH("*,"&A2,$E:$E,0)),"无",MATCH("*,"&A2,$E:$E,0))

完整代码

通过几个if语句判断返回查找内容的行号，ADDRESS(行号，列号)得到查找到的对应的类别地址，INDIRECT（地址）获取此地址的值

=INDIRECT(ADDRESS(IF(ISERROR(MATCH(A3&",*",$E:$E,0)),IF(ISERROR(MATCH("*,"&A3&",*",$E:$E,0)),IF(ISERROR(MATCH("*,"&A3,$E:$E,0)),IF(ISERROR(MATCH(A3,$E:$E,0)),"无",MATCH(A3,$E:$E,0)),MATCH("*,"&A3,$E:$E,0)),MATCH("*,"&A3&",*",$E:$E,0)),MATCH(A3&",*",$E:$E,0)),CELL("col",$E1)-1))