如果某个数据表中存在一对多匹配的情况,用 vlookup 通常只能查找出第一次匹配的结果,如果需要返回第 2 个匹配结果怎么实现?这个 2 可以是任意指定的次数。
案例:
下图中的 A、B 列是某公司员工的业绩表,这是一个累积的总表,每次有新的业绩出来,就顺序往下填写,先报先填。
如何查找出某人第 n 次申报的业绩?
解决方案 1:
1. 将 C 列设置为辅助列,在 C2 单元格输入以下公式 --> 下拉复制公式:
=COUNTIF(A$2:A2,A2)
公式释义:
统计相同的名字在列表中第几次出现
需要注意行号的绝对和相对引用
2. 在 G2 单元格中输入以下公式 --> 按 Ctrl+Shift+Enter 回车:
=VLOOKUP(E2&F2,IF(,A:A&C:C,B:B),2,0)
公式释义:
A:A&C:C:用 & 符号将 A、C 两列的值合并起来
IF(,A:A&C:C,B:B):将逻辑值 作为一组数组参与运算,从而扩充另一组数组 A:A&C:C,B:B,得到的结果为 {"赵铁锤1",353;"宋大莲1","134";...}
vlookup(E2&F2...):将 E2 和 F2 单元格的数据合并起来,成为 "赵铁锤3",用 vlookup 函数在上述数组中查找并返回第二列的值
数组公式,需三键结束
以下就是查找结果。
解决方案 2:
1. 构造辅助列,公式见解决方案 1。
2. 在 G2 单元格中输入以下公式 --> 按 Ctrl+Shift+Enter 回车:
=INDEX(B2:B25,MATCH(E2&F2,A2:A25&C2:C25,0))
公式释义:
E2&F2:合并 E2 和 F2 单元格的值
A2:A25&C2:C25:合并 A2:A25 区域和 C2:C25 区域成一组数组
match(...):将合并的单元格与数组中的结果匹配,并返回一个表示位置的数字
index(B2:B25,...):将 B2:B25 区域中,对应位置的值返回为结果
这也是个数组公式,因此也要三键结束
Excel学习世界
转发、在看也是爱!
领取专属 10元无门槛券
私享最新 技术干货