函数VLOOKUP实用技巧

说到学习Excel,只要掌握“4+1”就可以处理大部分数据处理问题。有四个核心函数:VLOOKUP、IF、SUM和SUMIF,还有一个核心函数:透视表。VLOOKUP功能是工作中最常用的搜索功能,掌握VLOOKUP功能可以大大提高工作效率。也是大多数朋友接触的第一个功能。几乎每天都在使用,使用频率很高。

但是大部分朋友都停留在基础用法上,也发现了VLOOKUP函数的一些不足,比如:无法反向查找,无法多条件查找,无法返回多列等等。我给大家分享一些使用VLOOKUP函数解决这些看似不可能的问题的技巧。

首先我们来看看VLOOKUP最基本的用法,为了方便大家做个图。

函数VLOOKUP实用技巧

总共只有四个参数,分别是:找谁,匹配对象范围,返回哪一列,匹配方法(0表示精确匹配,1表示模糊匹配)。VLOOKUP的基本单条件用法是简单用法,使用单个搜索关键词,搜索关键词在选择区的第一列,普通宣传就可以直接解决。概括起来,基本查询公式的用法是:

=VLOOKUP(找谁,去哪里找,找到后返回什么,怎么办)。

问题1:反向查找

反向查找和普通VLOOKUP查找有什么区别?我们都知道搜索关键词必须在查找区域的第一列,反向查找的搜索关键词不在查找区域的第一列。如果要进行更改,您可以使用虚拟数组公式。下图是一个例子:

函数VLOOKUP实用技巧

综上所述,反向查找的固定公式的用法:

=VLOOKUP(搜索关键字,IF({1,0},搜索关键字列,搜索值列),2,0)

这里详细讲解一下IF函数的数组应用部分:IF({1,0},$C$4:$C$16,$B$4:$B$16),其中涉及到Excel数组宣传的一些内容。

IF函数的第一个参数{1,0}是一个一行两列的数组常量,有两个元素;第二个和第三个参数是十三行一列的数组。数组扩展后,所有三个参数都变成了一个13行2列的数组,每个数组有26个元素:

函数VLOOKUP实用技巧

所以我们可以确定这个数组公式需要重复计算26次,返回一个十三行两列的数组。

在第一次计算中,分别取三个参数的第一个元素,形成通式=IF(1,& # 8221;C4 & # 8243;,”B4 & # 8243),根据数值类型自动转换的规律,1转换为逻辑值TRUE,所以计算结果为& # 8221;C4 & # 8243;结果是返回的数组中第一行和第一列的值;

在第二次计算中,分别取三个参数的第二个元素,形成通式=IF(0,& # 8221;C4 & # 8243;,”B4 & # 8243),根据数值类型自动转换定律,0转换为逻辑值FALSE,所以计算结果为& # 8221;B4 & # 8243结果是返回的数组中第一行第二列的值;

第三次计算,取三个参数的第三个元素,形成通式=IF(1,& # 8221;C5 & # 8243,”B5 & # 8243),计算结果是& # 8221;C5 & # 8243结果是返回数组中第二行第一列的值。

经过26次计算后,返回以下结果:

函数VLOOKUP实用技巧

以下是VLOOKUP函数的基本步骤,朋友们很容易理解。下面是IF函数的数组计算部分。请仔细理解,这对后面使用数组函数很有用。

问题2:多条件搜索

使用VLOOKUP匹配数据时,往往条件不是单个的,而是由多个组成的,所以也可以使用&字段拼接在一起,利用IF数组公式构造一个虚拟区域。下图是一个例子:

函数VLOOKUP实用技巧

综上所述,多条件搜索固定公式的用法:

=VLOOKUP(关键字1 & 2,IF({1,0},sequence 1 & 2,找到值的列),2,0)

注意:所有使用数组的公式都不能直接输入。你需要使用Ctrl+Shift+Enter,否则会出错。

问题3:返回多列查找

很容易查询一列。如果是多列呢?这时候就需要用到另一个辅助功能,列功能。有关列功能的简要介绍,请查看:

函数VLOOKUP实用技巧

COLUMN返回的结果是单元格引用的列数。例如,列(B1)的返回值是2,因为B1是第二列。

函数VLOOKUP实用技巧

综上所述,多列返回固定公式的用法:

=VLOOKUP(混合引用关键字,搜索范围,列(xx),0)

当您返回该列时,您可以开始引用该列中的单元格。

退货栏的商品和搜索区的不一样怎么办?比如先回毛利,再回销售的布局。这将使用匹配功能。介绍如下:

使用MATCH函数在区域单元格中搜索特定项,然后返回该项在该区域中的相对位置。例如,如果范围A1:A3包含值5、25和38,则公式=MATCH(25,A1:A3,0)得出数字2,因为25是该范围中的第二项。

VLOOKUP功能可以通过与其他辅助功能结合使用,以更多方式使用。和你的朋友一样聪明,开动脑筋,有新的发现。