在工作中，面对一张表格，我们经常需要做些查询工作。

比如，下图表格中，需要根据左边的部门人员清单，查找每个部门对应的所有人员姓名。

这是一个典型的一对多查询问题，根据部门查找多个对应的姓名。

很多函数高数都会给出 INDEX+SMALL+IF+ROW 的公式组合，如下：

• =IFERROR(INDEX($C$2:$C$11,SMALL(IF($B$2:$B$11=$E2,ROW($1:$10),4^8),COLUMN(A1))),"")

并且在公式编辑完成之后，还会「Ctrl+Shift+Enter」三键回车。

可是，作为一个大白，我完全看不懂复杂的函数公式。

稍有数据改动，就会束手无策。

那么，有什么其它简单易懂的方法吗？

答案是有的！

来，跟小 E 一起学习学习。

思路解析之 VLOOKUP

要解决这个问题，需要我们最熟悉的 VLOOKUP 函数。

但是 VLOOKUP 函数只会返回查找区域中，查找值第一个对应的值。

比如下图。

在查找人事部的人员姓名时，虽然林晨和张琴都是人事部门人员。

但是，VLOOKUP 函数只会返回「林晨」这一个值。

隔壁小美：你刚刚不是说，VLOOKUP 可以解决问题的吗，怎么又不行了？

小 E：小美，你别心急啊，听我慢慢跟你 Suo。

思路解析之改变查找值

既然 ，VLOOKUP 函数只会返回第一个对应值，那我们只需要将查找值，变得不同就可以了。

比如说将后面出现的「人事部」变成「人事部 1」、「人事部 2」……

然后分别查找「人事部 1」，「人事部 2」对应的人员姓名就可以了。

所以，我们要分两步来解决这个问题。

第一步：将查找值「人事部」变成「人事部 1」和「人事部 2」

「人事部」三个字是不变的，变化的是用来区分不同的后缀：1 和 2。

简单说，就是固定的「人事部」连接（&）变化的「数字」构成新的查找值来实现查找值的不同。

在此例中，我们可以使用 COLUMN 函数，返回指定单元格引用的列号，来实现后缀的变化。

COLUMN 函数，返回指定单元格引用的列号。

例如，公式 =COLUMN(D10) 返回 4，因为列 D 为第四列。

下图中，在 F9 单元格输入以下公式：

• =$E9&COLUMN(A1)
  

向右拖拽，由于单元格列号的变化，就会得到「人事部 1」，「人事部 2」，以此类推。

公式中要固定「人事部」，即 E9 单元格的列号要用「$」锁定。
「&」起连接作用。

思路解析之改变查找区域

第二步，要在查找区域中有「人事部 1」，「人事部 2」。

在下面这张表格中，A 列「辅助列」是我们新增的查找区域。

如何根据原本的 B 列「部门」，来生成我们需要的辅助列，是这一步的关键所在。

具体方法是：
在 A2 单元格输入公式：

• =B2&COUNTIF($B$2:B2,B2)
  

向下填充即可。

接下来，我们分析一下，公式为什么这样写。

首先，COUNTIF 函数，是用于统计满足某个条件的单元格的数量。

但这个公式的思路，不仅仅是使用 COUNTIF 函数，还是单元格的引用方式。

上图中 E9:E13 区域是第一次出现「人事部」，所以得到「1」；

E9:E17 区域是第二次出现「人事部」，所以得到「2」。

注意到这两个区域的开始部分都是 E9。

所以在生成辅助列的时候，开始区域是绝对引用，结束区域是相对引用。

这样得到的次数才会递增。

完成公式编写

经过上面几步的解析，我们已经完成了改变查找值和改变查找区域两步。

但是，当公式在 H3,查找「人事部 3」的时候：

查找区域中并没有「人事部 3」，就会返回 #N/A 错误。

所以，还需要使用 IFERROR 函数，来屏蔽错误值。

最终 ，F1 单元格的公式是：

• =IFERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$11,3,0),"")

呀，这么复杂的问题终于解决了！

赶紧向小美炫耀一下我的厉害！

     

总结

针对上面的问题，我总结出了两点结论：

❶ 对于一些复杂问题，我们可以使用辅助列和比较熟悉的函数来解决。

❷ 写公式的时候，思路很重要。先有思路，才会有公式。

Excel 里的技巧还有很多，学习也很简单，可惜很多人都不知道~

所以我强烈推荐你加入《秋叶 Excel 3 天集训营》，仅需 1 元，你就能学到超多 Excel 小技巧，以及表格设计思路！

秋叶 Excel 3 天集训营

课程原价 99 元

 现在 仅需 1 元！

实操练习+名师带学

3 天提升你的 Excel 能力！

赶紧扫码抢课吧！

👇👇👇

🎁

*广告