快捷搜索:  汽车  科技

excel多条件查询vlookup函数(掌握了这个套路)

excel多条件查询vlookup函数(掌握了这个套路)=VLOOKUP($G2&(COUNTIF($B$2:$B$29 $G2)-1) $A$1:$D$29 COLUMN(C1) 0)2. 在 H2 单元格中输入以下公式:1. 在 A 列前面新增一列,将其设置为辅助列,输入以下公式 --> 下拉复制公式:=B2&COUNTIF($B$2:B2 B2)公式释义:

用 vlookup 查找默认情况下是一对一出结果,如果要一对多查找,就需要用到各种技巧,具体方法我写过非常多了,可以搜索一下历史记录。

只要掌握了今天这个套路,无论你想查找第几次重复值,都易如反掌。

案例:

下图 1 中左侧的数据表是销售人员的各项产品销量流水,每位销售有多个销售记录,请按 E 和 F 列的要求,匹配出对应的产品和交易数。

效果如下图 2 所示。

excel多条件查询vlookup函数(掌握了这个套路)(1)

excel多条件查询vlookup函数(掌握了这个套路)(2)

解决方案:

1. 在 A 列前面新增一列,将其设置为辅助列,输入以下公式 --> 下拉复制公式:

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

公式释义:

  • COUNTIF($B$2:B2 B2):计算 B2 单元格的姓名在区域内是第几次出现;起始单元格必须绝对引用,其余单元格要相对引用;
  • =B2&...:将姓名与其出现的次数连接在一起,使得辅助列中没有重复值

excel多条件查询vlookup函数(掌握了这个套路)(3)

excel多条件查询vlookup函数(掌握了这个套路)(4)

excel多条件查询vlookup函数(掌握了这个套路)(5)

2. 在 H2 单元格中输入以下公式:

=VLOOKUP($G2&(COUNTIF($B$2:$B$29 $G2)-1) $A$1:$D$29 COLUMN(C1) 0)

公式释义:

  • COUNTIF($B$2:$B$29 $G2)-1:计算 G2 单元格的姓名在 B 列中出现的总次数,用总次数 -1,即可得出倒数第二次出现的次数;
  • $G2&...:用姓名跟上述次数连接起来,即可用于跟辅助列进行匹配;
  • $A$1:$D$29:查找区域;
  • COLUMN(C1):取出 C1 的列值,结果为 3,也就是结果列位于区域中的第 3 列;用 column 函数的好处是向右拖动公式时公式结果自动会变成 4,而不需要手工修改参数了;
  • 0:表示绝对匹配

excel多条件查询vlookup函数(掌握了这个套路)(6)

excel多条件查询vlookup函数(掌握了这个套路)(7)

3. 向右拖动公式。

excel多条件查询vlookup函数(掌握了这个套路)(8)

4. 拖动下拉 H2 单元格,将其公式复制到 H3 单元格 --> 将公式中的“-1”删除:

=VLOOKUP($G3&(COUNTIF($B$2:$B$29 $G3)) $A$1:$D$29 COLUMN(C2) 0)

公式释义:

  • 因为这里需要匹配倒数第 1 次,所以 countif 求出的结果就是最后一次,不需要再 -1。

excel多条件查询vlookup函数(掌握了这个套路)(9)

excel多条件查询vlookup函数(掌握了这个套路)(10)

5. 向右拖动单元格,复制公式。

excel多条件查询vlookup函数(掌握了这个套路)(11)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

猜您喜欢: