用两个条件匹配数据，这里有8种方法

图片来自：https://www./zh-cn/

工作中，我们常常遇到按照两个条件进行数据匹配（或查询）的情况。

▲ 表1：数据源

例如，根据表1数据源的日期和科目来匹配费用到表2，我们权且把这种匹配称为二维到一维匹配。

▲ 表2：目标表

又或者反过来，也有可能根据一维表（下表3）：

       

▲ 表3：数据源

匹配费用数据到表4：

▲ 表4：目标表

我们也把这种匹配先称之为一维到二维。

今天，我来介绍一、二维表格数据匹配的几种方法。

---

01 

二维到一维

方法1：INDEX+MATCH

       

首先，用MATCH函数分别求出数据行的日期和科目在数据源日期列（B3:B9）和科目行（C2:P2）的位置，然后在C3:P9这个区域利用INDEX索引函数，找到指定行和列对应的数值。

方法2：VLOOKUP+MATCH

       

先用MATCH找到科目所在列，再用VLOOKUP根据日期来查找（注意，第3个参数MATCH的结果要加1，因为第1列是从B列算起的）。

方法3：HLOOKUP+MATCH

       

方法3与方法2类似，但是先用MATCH找到日期所在行，再用HLOOKUP根据科目来匹配费用。

方法4：OFFSET+MATCH  

  

OFFSET函数不仅可以返回一个区域，也可以返回一个单元格。所以，当OFFSET最后两个参数为1时，即可返回特定位置的数据。

OFFSET函数可以根据偏移量返回数据，它有5个参数：

参数1：起始位置

参数2：向下移动X行

参数3：向右移动Y列

参数4和参数5：从新的位置开始返回一个M行N列的区域

02

一维到二维

▲ 数据源：B2:D51

反过来，从一维到二维，又有哪些方法呢？

方法1：SUMPRODUCT     

       

公式：SUMPRODUCT(($B$3:$B$51=$B59)*($C$3:$C$51=C$58)*$D$3:$D$51)

利用SUMPRODUCT进行条件判断，判断数据源日期和科目字段中每个单元格是不是要查询的日期和科目，对满足条件的数据进行先乘积再求和，最后就得到费用。

由于存在查询不到数据的情况（比如2010年职工薪酬），用SUMPRODUCT计算的结果为0，因此可以用IF函数做一下处理，使结果为0的显示为空。

方法2：VLOOKUP+IF{1,0}

公式：

{VLOOKUP($B71&C$70,IF({1,0},$B$3:$B$51&$C$3:$C$51,$D$3:$D$51),2,)}

利用IF{1,0}构造一个数据源，用日期&科目作为查找值进行查找，这是数组的用法，公式要加上大括号。

当查找不到结果的时候，此公式会显示错误值，因此再嵌套IFERROR进行处理。

方法3：SUMIFS

公式：SUMIFS($D$3:$D$51,$B$3:$B$51,$B93,$C$3:$C$51,C$92)

       

把匹配问题转化成多条件求和问题（当然只能针对数值的情形，对文本不适用）。

方法4：自定义函数VLOOKUPS

公式：Vlookupifs(2,$D$3:$D$51,$B$3:$B$51,$B82,$C$3:$C$51,C$81)

本例中，相当于我们根据日期和科目两个条件去做查询，不管用SUMPRODUCT还是SUMIFS，都是把匹配问题转化成了条件求和问题，因此只能对查询结果是数值的情形适用。如果是文本则不合适。

因此，我用VBA编写了一个自定义函数——VLOOKUPIFS，多条件匹配，可以根据多个条件来匹配目标（不管目标是数值还是文本都可适用）。

当然，自定义函数必须添加到加载宏里才可以在任何文件中使用。