excel函数技巧：一对多查找的典型案例分析

编按：大冬天里，经常有排着长队的供应商跺着脚等着拿入库单。库管部的两个制单员忙得一塌糊涂，还经常被供应商投诉。果冻布丁同学就是其中之一，她决定改变这种状况，向老师求助怎么根据一个单号自动生成一张入库单……最终通过解决一对多查询她得到了供应商群里的最大点赞！

最近我们的果冻布丁同学遇到了一个关于入库单的问题。到底是什么样的入库单让她这么抓狂呢？

果冻布丁同学的需求是只需要在入库单I2单元格中填入红色的单号就可以从明细表中自动查找对应的物料名称规格、价格等数据并填充好，然后将其打印出来。

入库明细表：

入库单：

由于一个单号对应了多个物料，所以果冻布丁同学的问题就是典型的一对多查询问题。

解决思路：

首先我们看到入库信息表中的单号与项目编号是有相关性的，项目编号是由单号加上0-9数值依次顺序组成，项目编号与物料信息数据一一对应。所以我们可以将单号与数字的组合即项目编号而不是单纯的单号作为查找值。

函数公式：

=IFERROR(VLOOKUP($I$2&ROW(单据打印!M1)-1,入库!$C:$K,COLUMN(C1),0),"")

将函数公式向右向下填充即可。

公式解析：

（1）$I$2&ROW(单据打印!M1)-1：用于将编号与数字0、1、2等组合变成项目编号20181113A10、20181113A11、20181113A12等。ROW用于获取行号，由于其返回的结果最小值只能为1，想要从0开始就需要-1。M1，只是用来协助获取行号，可以是B1、L1、H1等任何第一行的单元格。

（2）COLUMN(C1)含义是返回C1所在单元格的列序号，即3。使用函数COLUMN的目的是为了让VLOOKUP第三个参数由静态的数字变成动态的数字，达到批处理效果。如当整体函数公式向右填充时COLUMN(C1)变成COLUMN(D1)，返回4。

（3）IFERROR函数的作用是为了规避vlookup函数统计的错误结果，如果遇到#N/A这样的错误则返回空白。

其实excel和数学题一样，一题多解那是必须的。

既然VLOOKUP能解决，那么INDEX+MATCH、OFFSET函数应该也能解决果冻布丁同学的问题。

INDEX+MATCH函数公式：

=IFERROR(INDEX(入库!E:E,MATCH(单据打印!$I$2

这里的函数公式我们就不做详细的解释，INDEX+MATCH组合相信大家应该很熟悉了。

函数公式的重点与方案基本一致，都是通过将单号合并ROW返回的数值作为查找值，以此匹配入库单中C列对应的物料信息。

果冻布丁同学的问题得到了解决。原来排长队等拿入库单的现象基本消失，在供应商群里，很多人给她点赞：姑娘，谢谢啦！

总结：

VLOOKUP函数本身的确是不能进行一对多查找的。上面2个方案都是通过给相同的查找值加个小尾巴——标号，来区分，这样就能通过一对一的方式完成查找。所以大家遇到类似果冻布丁同学的问题时思维要变换一下，把一对多变成一对一。另外，本例中我们巧妙的通过ROW、COLUMN两个函数公式替代了静态数字，让公式得以批量向右向下填充。大家以后记得多多运用哦！