微精选

欢迎转发和点一下“在看”，文末留言互动!

置顶公众号或设为星标及时接收更新不迷路

朋友们好，这里是EXCEL应用之家，坚持分享ECXEL操作技巧。

今天要和大家分享一组高版本函数的文本题目。题目是这样的：

题目要求根据上方的表格，根据姓名来查找所属区域。这道题目，用低版本函数INDEX函数嵌套IF函数就可以完成。今天我们将着重分享一组高版本的函数技巧。

在单元格B11中输入下列公式，并向下填充即可。

=CONCAT(REPT(A$1:E$1,COUNTIF(A11,A$2:E$8)))

一句话解释：

由于名称是唯一的，COUNTIF函数返回一个包含一组0和一个1的7行5列的内存数组。接下来REPT函数返回1对应的区域名称，其余的返回空值。最后由CONCAT函数完成文本合并。

在单元格B11中输入下列公式，并向下填充即可。

=TOCOL(IF(A$2:E$8=A11,A$1:E$1,A),2)

一句话解释：

IF函数部分第三参数是不符合EXCEL公式中对于文本输入要求的，会返回错误值。但这里就是要返回错误值。TOCOL函数以一列形式返回数组，参数2代表忽略错误值。

在单元格B11中输入下列公式，并向下填充即可。

=XLOOKUP(1=1,BYCOL(A$2:E$8=A11,OR),A$1:E$1)

这条公式比较有意思。

BYCOL(A$2:E$8=A11,OR)

BYCOL函数官方的解释是，将LAMBDA函数应用于每个列并返回结果的数组。什么意思呢？字面上不好理解，BYCOL函数通常要和LAMBDA函数配套使用，在这里可以这样理解：

它的第一参数必须是一个数组。A$2:E$8=A11返回一个7行5列的数组。

它的第二参数是作用在数组中每一列的函数计算。例如，这里第二参数是OR，表明对每一列都进行OR函数运算。

BYCOL函数的第二参数类似SUBTOTAL函数或者AGGREGATE函数，有多个函数可供选择。

整体上BYCOL函数返回一个1行5列的数组，{FALSE,TRUE,FALSE,FALSE,FALSE}。

XLOOKUP(1=1,BYCOL(A$2:E$8=A11,OR),A$1:E$1)

接下来就简单了，利用XLOOKUP函数查找并返回TRUE对应的区域就可以了。

和BYCOL函数相同的，还有BYROW函数，用法是类似的。

在单元格B11中输入下列公式，并向下填充即可。

=XLOOKUP(A11:A12,BYCOL(A2:E8,CONCAT),A1:E1,,3)

解释一下这条公式。

BYCOL(A2:E8,CONCAT)

这个和上面那条公式中的BYCOL函数类似。只不过这里第二参数使用的是CONCAT，意思是给数组中的每一列都执行文本合并操作。

XLOOKUP(A11:A12,BYCOL(A2:E8,CONCAT),A1:E1,,3)

XLOOKUP函数第一参数查找值，第二参数查找区域，第三参数返回区域，第四参数找不到时的替代输入值，第五参数匹配模式，3代表正则表达式匹配。

正则表达式匹配顾名思义，可以在查找值中编写正则表达式来匹配和查找数值。由于查找区域是合并后的文本字符串，查找值中的姓名就是一种简单的正则表达式。

在单元格B11中输入下列公式，并向下填充即可。

=FILTER($1:.$1,BYCOL(A11=A$1:E$8,OR))

这条公式中也有一个技巧。BYCOL函数不必再解释了，它返回的结果是{FALSE,TRUE,FALSE,FALSE,FALSE}。说说FILTER函数。

$1:.$1

这是对第1行的引用。通常情况下，这种引用会有多个空白单元格。而$1:.$1这种写法裁剪掉了引用区域内多余的空格，只返回有实际数据的引用。因此，这部分返回的结果是{“华北”,”华东”,”华南”,”华中”,”西南”}，

FILTER($1:.$1,BYCOL(A11=A$1:E$8,OR))

最终，FILTER函数返回“TRUE”对应的区域“华东”。