Excel教程

Excel函数
Excel技巧

【EXCEL教程】Vlookup函数一对多查找大法,EXCEL神技巧!

当前位置:网站首页 > Excel教程

【EXCEL教程】Vlookup函数一对多查找大法,EXCEL神技巧!

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-03-06 21:03:15 * 浏览: 34

常规情况下利用Vlookup查找,当数据源有多条结果时,Vlookup也只返回第一条查询结果。

这是为什么呢?首先来看一下原厂说明书中 Vlookup 的语法及参数简释

    语法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    参数简释

    lookup_value  必需。要在表格或区域的第一列中搜索的值。(也就是一参数查找的值要参数二table_array的第一列中

    table_array  必需。包含数据的单元格区域。(第一列中的值是 lookup_value 搜索的值)

col_index_num  必需。返回参数 table_array 中第几列的值。

    range_lookup  可选。一个逻辑值,指定希望 VLOOKUP 查找精确匹配值还是近似匹配值。

    要点:如果 table_array 的第一列中有两个或更多值与 lookup_value 匹配,则使用第一个找到的值。


说明书中要点里有说明第一列中有两个或更多值与查找值匹配时,则使用第一个找到的值。


SO:当遇上一对多查询时很多小伙伴就不淡定了

网上搜来的一对多查找

Index+Small+If+Row公式实在太难理解,公式辣么辣么长还得三键结束。问题是我还看不懂啊!

有没有简单易懂又不用数组的方法呢?

答案肯定是有的

今天小编就来给大家分享一种不用数组公式的一对多查询

Vlookup+辅助列

如下图所示,根据姓名查找对应的每笔销售明细

首先,我们在姓名前面加一列辅助列

输入公式=B2&COUNTIF($B$2:B2,B2)

公式COUNTIF($B$2:B2,B2)对指定区域$B$2:B2中指定条件的单元格B2计数,

这样相当于给姓名编了个号(让每个姓名都唯一),第一个$B$2 使用了绝对引用,目的在于下拉的时候起点不变,终点一直变,这样就能统计出区域中有几个一样的名字了。如图中第一次出现的阿文就是阿文1,第二次出现的阿文就是阿文2

不明白的童鞋自己动手试一试哦!可不可以1阿文,2阿文呢?


然后在G2中使用公式

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0)

往下拉到足够多行(比如最大10条数据,你可以拉15行,备用5行)

效果如下图:



通过颜色对比,我们可以校验一下返回的结果对不对

公式中函数 Row(A1) 返回A1所在的行号,作用是给查询值加上一个序号,比如:F2为雨夜时,公式下过程中查找值变成了找雨夜1、雨夜2。。。实现了每行查询的都是唯一值。

第一列日期“没问题”了,就开始写第二列销售量的公式,它和和一列的区别仅仅是返回的数据列不一样,

H2中输入公式

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,4,0) 看一下效果


我们把两个公式放一起看看区别吧

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0)

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,4,0)

明显的区别就是参数3 里的一个是3,一个是4,如果还需要5,6,7,8列的话就复制粘贴接着改。

有的小伙伴看到这里是不是在偷笑了,小编原来还在复制粘贴改数字呀。要是遇上列数很多肿么办?

我们先把难看的 #N/A 给解决掉,回头再来处理它。

公式外套个IFERROR容错函数来屏蔽错误值,找不到时返回空

 =IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0),"")

一起来看公式的效果图


怎么样,是不是“完美”了呢?

    

接下来改参数3,这里可以用column函数,把参数3换成 column(C$1) 因为C列是第3列,因此该函数会返回3,当我们右拉一列它会变成column(D$1),这正好返回4 ,真是太巧了,正好是我需要的3和4。

公式如下

=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$20,COLUMN(C$1),0),"")

咱们来看看效果



可有的小伙伴还会有疑问,要是查找时返回的列号是不规律的,这要怎么办呢??

那就得请到我们的Match来帮忙数数了,感兴趣的同学自己动手试试吧。(可以参考阿文老师的高大上的动态图表中MATCHT的用法哦)

今天的分享就到这,如果教程对大家有用,希望大家多多分享点赞支持小编哦!你的每一次点赞和转发都是支持小篇坚持原创的动力。