多个多条件查找vlookup在列，如何用函数匹配多个值？ 示例文件地址： https://u5k.cn/vt7wu

来源：蜘蛛抓取(WebSpider) 时间：2023-12-16 14:30 标签： 多条件查找vlookup

我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。
下面是3个示例工作表：
图1：工作表Sheet1
图2：工作表Sheet2
图3：工作表Sheet3
示例要求从这3个工作表中从左至右查找，返回Colour列中为“Red”且“Year”列为“2012”对应的Amount列中的值，如下图4所示的第7行和第11行。
图4：主工作表Master
解决方案1：使用辅助列
可以适当修改上篇文章中给出的公式，使其可以处理这里的情形。首先在每个工作表数据区域的左侧插入一个辅助列，该列中的数据为连接要查找的两个列中数据。这样，获取值的数组公式（单元格C7）如下：
=VLOOKUP(A7&” ”&B7,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!A:A”),A7&” ”&B7)>0,0))&”‘!A1:D10″),4,0)
其中，Sheets是定义的名称：
名称：Sheets
引用位置：={“Sheet1″,”Sheet2″,”Sheet3”}
这个公式的运行原理与上文相同，可参见《Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）》。
解决方案2：不使用辅助列
首先定义两个名称。注意，在定义名称时，将活动单元格放置在工作表Master的第11行。
名称：Arry1
引用位置：=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)
名称：Arry2
引用位置：=ROW(INDIRECT(“1:10”))-1
在单元格C11中的数组公式如下：
=INDEX(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”),MATCH(1,(T(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”),Arry2,,,))=$A11)*(N(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”),Arry2,,,))=$B11),0))
下面来看看公式是怎么运作的。首先看看名称Arry1：
=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)
可以转换为：
=MATCH(TRUE,COUNTIFS(INDIRECT({“‘Sheet1’!B:B”,”‘Sheet2’!B:B”,”‘Sheet3’!B:B”}),”Red”,INDIRECT({“‘Sheet1’!C:C”,”‘Sheet2’!C:C”,”‘Sheet3’!C:C”}),2012)>0,0)
转换为：
=MATCH(TRUE,{0,0,1}>0,0)
结果为：
3
表明在工作表列表的第3个工作表（即Sheet3）中进行查找。
因此，在单元格C11的公式中的：
INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”)
转换为：
INDIRECT(“‘”&INDEX(Sheets,3)&”‘!D1:D10”)
转换为：
INDIRECT(“‘”&INDEX({“Sheet1″,”Sheet2″,”Sheet3″},3)&”‘!D1:D10”)
转换为：
INDIRECT(“‘”&”Sheet3″&”‘!D1:D10”)
转换为：
INDIRECT(“‘Sheet3’!D1:D10”)
结果为：
Sheet3!D1:D10
传递到INDEX函数中作为其参数array的值：
=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”),Arry2,,,))=$A11)*(N(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”),Arry2,,,))=$B11),0))
同样，公式中的：
INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”)
得到：
Sheet3!B1
公式中的：
INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”)
得到：
Sheet3!C1
现在，单元格C3中的公式变为：
=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))
由于这里的两个公式结构：
T(OFFSET(Sheet3!B1,Arry2,,,))=$A11
和
N(OFFSET(Sheet3!C1,Arry2,,,))=$B11
相似，因此只解释其中一个的工作原理。
先看看名称Arry2：
=ROW(INDIRECT(“1:10”))-1
由于将在三个工作表中执行查找的范围是从第1行到第10行，因此公式中使用了1:10。
上述公式转换为：
{1;2;3;4;5;6;7;8;9;10}-1
得到：
{0;1;2;3;4;5;6;7;8;9}
该数组被传递给OFFSET函数作为其rows参数，这样：
OFFSET(Sheet3!B1,Arry2,,,)
将会生成：
Sheet3!B1
Sheet3!B2
Sheet3!B3
…
Sheet3!B10
因此，公式：
T(OFFSET(Sheet3!B1,Arry2,,,))=$A11
转换为：
T(OFFSET(Sheet3!B1,{0,1,2,3,4,5,6,7,8,9},,,))=$A11
转换为：
T({Sheet3!B1,Sheet3!B2,Sheet3!B3,Sheet3!B4,Sheet3!B5,Sheet3!B6,Sheet3!B7,Sheet3!B8,Sheet3!B9,Sheet3!B10})=$A11
转换为：
{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=$A11
转换为：
{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=”Red”
得到：
{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}
注意，如果你在这里使用的是N函数：
N(OFFSET(Sheet3!B1,Arry2,,,))
其结果将为：
{0,0,0,0,0,0,0,0,0,0}
当然，也不能够单独只使用OFFSET函数：
OFFSET(Sheet3!B1,Arry2,,,)
其结果将为：
{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
同样地，公式中的：
N(OFFSET(Sheet3!C1,Arry2,,,))=$B11
转换为：
{0,2010,2010,2012,2012,2012,2012,2011,2014,2011}=2012
结果为：
{FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}
好了！现在可以将上面得到的中间结果放到主公式中：
=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))
转换为：
=INDEX(Sheet3!D1:D10,MATCH(1,({FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE})*({FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}),0))
转换为：
=INDEX(Sheet3!D1:D10,MATCH(1,{0,0,0,0,1,0,0,0,0,0},0))
转换为：
=INDEX(Sheet3!D1:D10,5)
结果为
32
vlookup函数是excel表格最为常用的函数之一，它的作用就是查找某个值在区域中对应位置的另一个值，实际就是通过在区域的首列来返回查询值的位置，然后对应区域其他列中对应单元格位置的值。它的常规表达式为：=vlookup（查找值，查找区域，返回列，匹配类型）如果查找值在查找列出现了多次，那么返回结果默认为第1次出现的值对应的单元格数据。语法图如下所示：了解了vlookup函数的基础语法，我们来看看今天的任务。如下图所示，我们要查找当天西瓜的第2次销量。这个问题字数不多，问题很明了，就是查询第2次出现的数据，那么使用vlookup函数，明显与它的基础语法规则不符，因为vlookup函数默认只查询第1次出现的数据。那么这种情况，我们是不是要另寻其他引用函数，来解决这个问题。其实不然，虽然vlookup函数的运算规则不会变，但它的参数可以变，我们数据表的源数据可以变。当然，源数据不会发生无序变化，我们接下来只是通过建立辅助列，来建立一个新的数据列，并作为后续vlookup函数的参数来进行计算。那首先我们创建一个辅助列，并输入公式1：=COUNTIF($D$2:D2,D2)这是一个countif函数单条件计数公式，它的第1参数条件区域借用了C列品种列表，并且单元格的引用方式是混合引用，即首个单元格绝对引用，结束单元格为相对引用，这种引用方式，通常会跟随公式的填充，得到一个延伸扩展的单元格区域，而在公式计算时，获取到一个更新的结果。购买专栏解锁剩余51%Excel办公技能从初学到进阶，成为工作达人优质教育领域创作者,活力创作者所属专栏Excel100个常见场景可套公式作者：Excel教程学习专栏简介：excel100个常见场景可套公式，快速提高excel函数公式灵活应用水平 27.9元 立即购买本专栏
编按：通过日期和另一个条件如姓名进行查找，但是辅助列中日期变成了数字，那这时还能用Vlookup进行查找吗？还有，能不能不用辅助列进行多条件查找呢？一、日期迷思最近有学员在使用vlookup进行双条件查找时遇到了困难：归纳起来就是两个问题：添加“姓名&日期”辅助列，但是辅助列的结果始终不对，这是怎么回事？能不能不用辅助列实现姓名和日期双条件查询？通常使用Vlookup函数进行多条件查找的时候，使用辅助列是个不错的选择。使用“&”符号将两列或者多列值连接生成辅助列，然后再用“&”符号将两个或者多个条件串起来当作查找值即可。这位同学的第一解决方案也正是这样的：添加“姓名&日期”辅助列，然后用Vlookup查询“G2&H2”。可是当同学发现辅助列原本应该得到姓名加日期的，却变成了姓名加数字，于是就不敢继续使用Vlookup查询了。他完全被这个莫名其妙的日期变化给弄迷糊了！！二、vlookup并没有失效或许类似的问题大家也遇到过，其实完全不必被这个日期数字的变化给吓到，给迷惑，vlookup并没有那么不堪：从上图可以看到，虽然辅助列姓名后不是日期，但我们使用vlookup进行查找还是能得到正确的结果，这是怎么回事呢？三、日期与数字之间的秘密日期与数字之间到底有何联系，我们通过一个小测试就能明白了：明白了吗？将日期所在的单元格改成常规格式，日期就变成数字了，所以经常会听到这样一种说法：日期本质是数字！关于日期和数字之间的关系，懂得下面这几个要点就可以了：1.日期是一种特殊形式的数据，一个日期对应一个常规数字，但是反过来一个数字不一定对应一个日期；2.在EXCEL中日期是有范围的，从1900-1-1到9999-12-31，对应的数字是1到2958465，超出范围就显示错误；注意：0虽然可以转换为日期，但是这个日期并没有实际意义；当数字超出范围后，转为日期格式时会显示为一串#。3.想要知道数字代表哪个日期，或者是日期等于哪个数字，通过设置单元格格式为日期或者常规即可；4.通常在公式中，日期被当成数字处理。四、可以用TEXT函数把数字显示为日期明白了上面的几点，自然就知道虽然辅助列姓名后是数字而非日期，但仍然可以使用Vlookup函数正常查找。如果非要姓名后的数字显示为日期不可，需要使用TEXT函数进行强制显示：公式为：=B2&TEXT(C2,"e年m月d日")TEXT函数的用法很简单：TEXT(数据,”格式代码”)，其作用就是将数据按照指定的格式显示出来。格式代码一定要加引号（英文状态）。e表示年（也可以用yyyy表示年），m表示月，d表示日。到此，同学的第一个问题就解决了。下面看看第二个问题。五、不用辅助列Vlookup多条件查找第二个问题，能否不用辅助列使用Vlookup进行多条件查找？完全可以！我们既然可以用“&”符号把两个条件连接起来当成一个查找值使用，当然也有办法把查找区域中的B列、C列当成1列来使用。使用IF函数数组形式，我们可以把查找范围B:D变成B&C:D，然后进行查找：注意：公式=VLOOKUP(F2&G2,IF(,B2:B34&C2:C34,D2:D34),2,0)是一个数组公式，输入后须要按Ctrl+Shift+Enter，然后再向下填充公式，否则公式填充后结果可能显示为错误#N/A。＊＊＊说明：本文主要由老菜鸟写作。小雅完成第五节。＊＊＊＊部落窝教育－excel多条件查找＊＊＊＊原创：老菜鸟、小雅/部落窝教育（未经同意，请勿转载）更多教程：部落窝教育（http://www.itblw.com）微信公众号：exceljiaocheng

我要回帖

更多关于 多条件查找vlookup 的文章

 

随机推荐