EXCEL中的vlookup多条件查询

EXCEL中的vlookup多条件查询

 

在EXCEL里面有多个方法可以完成多条件查询，先来了解合并数组这个概念：

如果Sheet2是这样的：

A  ,B  ,C 

张三，一月，90

张三，二月，85

张三，三月，95

那么If({1,0},Sheet2!A1:A3&Sheet2!B1:B3,Sheet2!C1:C3)就是：

张三一月，90

张三二月，85

张三三月，95

也可以用Choosen({1,2},Sheet2!A1:A3&Sheet2!B1:B3,Sheet2!C1:C3),结果相同。现在就可以用vlookup来进行多条件查询了：

{=vlookup(A1&B1,If({1,0},Sheet2!A1:A3&Sheet2!B1:B3,Sheet2!C1:C3),2,false)}

还可以用sum函数直线多条件合计功能：

{=sum((Sheet2!A1:A3=A1)*(Sheet2!B1:B3=B1)*Sheet2!C1:C3)}

这个功能也很有意思，另外还可以用index match来解决，有一个值得一提的小技巧是，对于长的公式需要同时按Ctrl Shift Enter来给单元内容加上{}

 

多条件应用场景：

          Vlookup的函数的正常用法我就不说了，大家都很清楚。都知道Vlookup是在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。就是说的条件只有一个，如果遇到一个以上的条件应该怎么办呢？如下图示例（Sheet1中的A1:C5为源数据，要求在Sheet2中输入字段1与字段2，自动使用Vlookup得到字段3）：

可以在Sheet2表中的C2单元格写如下公式，即可达到要求（是数据公式，以Ctrl+Shift+Enter结束输入。本文中涉及的基本上都是数组公式）：

=VLOOKUP(A2&B2,IF({1,0},Sheet1!$A$2:$A$5,"")&Sheet1!$B$2:$C$5,2,0)

多条件公式分析：

          第一个参数：这个不用说了，即把Sheet2中的A2与B2连接起来当第一参数；

          第二个参数：这个是主要分析的地方，我们把公式折分成两部分来看：

                     第一部分：IF部分。IF({1,0},Sheet1!$A$2:$A$5,"")。即利用IF与辅助的空字符串将Sheet1表中的A2:A5部分变成一个4行2列的二维数组(数组第二行全是空字符串)，即下图中的“IF部分”；

                    第二部分：剩下的部分。Sheet1!$B$2:$C$5。Sheet1表中的B2:C5部分组成的4行2列二维数组，即下图中的“Sheet1!B2:C5部分”；

                    把第一部分中4行2列的二维数组与第二部分中4行2列的二维数据合并后得到新的4行2列的二维数组，正是Vlookup的第二个参数所需要的内容，即下图中“Vlookup第二个参数”部分。

           第三、四参数就不说了，正常的使用。

          如果有三个或者更多条件，以此方法类推就可以了。

          因为此种情况在日常统计工作中还是比较常见的，所以整理了一下贴出来，大家看看，有好的建议或者意见都欢迎您留言，谢谢！

          欢迎转贴，请注明原出处地址：http://hi.baidu.com/ckec/blog/item/60a091ec510f6c4778f055b4.html

附上Vlookup函数的说明：

VLOOKUP

在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。

VLOOKUP 中的 V 表示垂直方向。当比较值位于需要查找的数据左边的一列时，可以使用 VLOOKUP，而不用 HLOOKUP。

语法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value 为需要在表格数组 （数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）第一列中查找的数值。Lookup_value 可以为数值或引用。若 lookup_value 小于 table_array 第一列中的最小值，VLOOKUP 将返回错误值 #N/A。

Table_array 为两列或多列数据。请使用对区域的引用或区域名称。table_array 第一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。不区分大小写。

Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num ：

小于 1，VLOOKUP 返回错误值 #VALUE!。
大于 table_array 的列数，VLOOKUP 返回错误值 #REF!。
Range_lookup 为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值：

如果为 TRUE 或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。
table_array 第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值。可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排序。有关详细信息，请参阅默认排序次序。

如果为 FALSE，VLOOKUP 将只寻找精确匹配值。在此情况下，table_array 第一列的值不需要排序。如果 table_array 第一列中有两个或多个值与 lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值 #N/A。