|
在Excel中运用公式查找数据时，大部分人首先想到的是Vlookup。Vlookup可以说是大众情人了，在查找数据时方便快捷。但是Vlookup通常情况下是进行正向查找，即条件列在前面，需要返回的结果列在后面。但是有时候，我们要查找的数据在前面，与查找条件匹配的条件区域在后面，也就是说我们要进行反向查找，这可是让很多人都觉得麻烦的问题。那么你知道怎样进行反向查找吗？常用的反向查找套路有哪些？今天我们就来说说反向查找那些事儿。 Index Match结构 Index Match是按条件查找中一个常见的公式组合，它不仅可以完成单条件查找，也可以完成多条件查找。由于返回结果列、条件列分别在两个参数里面，所以这个组合对顺序没有要求。 以下是数据，我们需要查找某个客户对应的销售员是谁。 应用公式如下： =INDEX(A:A,MATCH(G2,B:B,0)) Index的第一个参数指明从哪里返回数据，第二个参数指明数据所在的位置。第二个参数由Match函数确定要查找的内容（客户名称）在B列的位置。Match函数的第三个参数是0表示精确查找，这个是可选参数，大家不要漏掉了，因为漏掉这个参数就表示模糊匹配了，返回结果可能就不是你想要的了。 如果要应用多条件查找，就需要使用数组形式了。 比如以下公式，根据客户和发货城市两个内容确定销售员，这是数组公式，需要按Ctrl Shift Enter结束。 =INDEX($A$1:$A$9,MATCH(G2&H2,$B$1:$B$9&$D$1:$D$9,0)) Offset Match结构 Offset函数的作用是根据起始点，指定横向、纵向移动的距离，返回一个指定行高、列宽的区域。所以这里我们可以从结果列的第一个单元格开始，确定需要返回结果所在的位置，然后偏移相应的行数就可以得到结果了。 公式如下。 =OFFSET(A1,MATCH(G3,B:B,0)-1,) 如果要实现多条件查找，Match的用法和上面介绍的一样。 =OFFSET($A$1,MATCH(G3&H3,$B$1:$B$9&$D$1:$D$9,0)-1,) 这也是一个数组公式。 Indirect Match结构 其实不管用Index、Offset还是Indirect，都需要用Match确定要返回的值所在的行，然后根据不同函数的特点来更改就可以了。Indirect函数的作用是根据文本形式的地址返回该地址的引用。也就是说我们只要生成结果所在的单元格地址就可以得到具体的内容了。比如结果在A5单元格，其中“A”是固定的，只要用Match函数得到结果所在的行号就可以了。 =INDIRECT('A'&MATCH(G4,B:B,0)) 如果是多条件查找的话，道理跟上面一样，这也是一个数组公式。 =INDIRECT('A'&MATCH(G4&H4,$B$1:$B$9&$D$1:$D$9,0)) Lookup结构 Lookup有很多经典用法，可以根据范围来查找，也可以用来精确查找。 Lookup采用二分法来查找数据，下面这个结构属于常见套路之一。公式第二个参数,0/(B1:B9=G3)，表示查找区域的值等于目标值时，返回0，否则返回错误值。在本例中，这个参数的值为： {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 公式第一个参数是1，表示从查找区域中找小于或等于1的最大值。在上面的参数值中我们可以看到中间有一个0，这个表示找到了符合的值。 这里要注意，如果存在多行匹配的内容，则会返回最后一个匹配的内容，如下图所示。 使用lookup也可以实现多条件查找。 =LOOKUP(1,0/(($B$1:$B$9=G3)*($D$1:$D$9=H3)),$A$1:$A$9) Vlookup if（{1,0},,)结构 Vlookup不能直接使用反向查找，但是我们可以重新“构造”数据区域，使区域符合Vlookup的规则。 如下图所示，1，0要写在大括号里面，{1,0}，If函数的第二个参数就表示从哪里查找，第三个参数表示从哪里返回值。这个结构的Vlookup第三参数都是2，因为我们用If构造了一个两列的数据。 =VLOOKUP(G4,IF({1,0},$B$2:$B$9,$A$2:$A$9),2,0)
使用这个结构也可以实现多条件查找。 =VLOOKUP(G4&H4,IF({1,0},$B$2:$B$9&$D$2:$D$9,$A$2:$A$9),2,0) 这个也是数组公式，需要按Ctrl Shift Enter结束。
Vlokup Choose({1,2},,)结构 这个公式跟Vlookup If({1,0},,)结构和原理比较相似，都是重新“构造”数据区域，一般情况下掌握一种即可。 =VLOOKUP(G5,CHOOSE({1,2},$B$2:$B$9,$A$2:$A$9),2,0)
同样的道理，这个也可以完成多条件查找。 =VLOOKUP(G5&H5,CHOOSE({1,2},$B$2:$B$9&$D$2:$D$9,$A$2:$A$9),2,0) 这个也是数组公式，需要按Ctrl Shift Enter结束。
这么多套路，怎么着也得会几个吧? --End-- QQ群 9735376 426619302 214641323 |
|
|
