Excel使用vlookup结合if二维数组对多列数据查询方法

最近同事有个需求,需要同时匹配三列单元格的值,匹配上后取他们对应的数值放在指定单元格上;下面详细介绍一下我是如何操作的。

A、B、C、D列为测试原始数据;
需求:F、G、H为目标数据列,I列的数据需要根据F、G、H列与A、B、C列相互匹配后,取D列的数据放在I列上。
下面截图举例说明:

这个公式的难点不在vlookup上面,而在以if函数表示的二维数组上。(下图是动画,单击后放大更清晰)

那么这个二维数组什么意思呢?

IF({1,0},$A$2:$A$7&$B$2:$B$7&$C$2:$C$7,$D$2:$D$7)

我来详细拆解说明:
(1)if函数语法

if(判断是否为真的条件,如果为真返回这个值,如果为假返回这个值)

if这个函数,就是判断是否为真逻辑条件;如果判断结果是真,那么就返回为真的值;如果判断结果是假,那就返回为假的值。这个函数比较简单且常用,我就不举例说明了。

(2){1,0}代表什么意思呢?
它是这个函数重点中的重点,意思让excel在内存中,临时组建一个二维数组,第一参数是“1”(excel默认已非0的数字为真,0的数字为假)判断为真,那么excel去取判断结果为真对应的值,这里对应上面的公式就是 “$A$2:$A$7&$B$2:$B$7&$C$2:$C$7”,第二个参数是“0”判断结果是假,那么excel去取判断结果为假对应的值,这里对应的上面公式就是“$D$2:$D$7”。好了,这个公式简单的部分说完了;现在说复杂的,{1,0}神马意思?这个意思就说让excel在内存中组建一个二维数组,把“A2:A7”和“B2:B7”和“C2:C7”放在A列上,把D2:D7放在B列的“B2:B7”上,组建之后的样式见下图。

这就是这个函数精妙之处,因为vlookup只能取一列数据查询,那么取多列怎么办,就用if的二维数组形式处理就好了,把多列临时在内存中合并成一列,这样交给vlookup就好了。

然后介绍这个函数中每个参数含义

VLOOKUP(F2&G2&H2,IF({1,0},$A$2:$A$7&$B$2:$B$7&$C$2:$C$7,$D$2:$D$7),2,0)

参数1,要查找的是“F2&G2&H2”,这就是合并这三列值;
参数2,查找值所在的区域IF({1,0},$A$2:$A$7&$B$2:$B$7&$C$2:$C$7,$D$2:$D$7)上面(2)已经详细介绍了;
参数3,区域中包含返回值的列号2,为什么是2呢?因为我们用if二维数组临时组建一个临时数据表,所以第2列就是上面二维数组中的“D2:D7”;
参数4,0代表为FALSE,为精确匹配查询结果;

最后结合上面文章中的三张截图,就懂了这个函数的意思啦,if二维数组应用的场合还很多,明白这个函数使用方法后,就可以举一反三啦应用在其他场合啦。

发表评论