一、XLOOKUP函数基本用法
VLOOKUP有许多先天限制,比如:在搜数据时,只能对右侧的数据进行单向搜寻,而不能向左并查询垂直方向的数据。此外,当表格中安插了新的栏位后,VLOOKUP也会受到影响,产生错误的结果。XLOOKUP克服了VLOOKUP所有的缺点,在使用上也很简单。XLOOKUP的基本函数结构如下:
示例:我有如下表格,想找到小明的国文成绩。
根据XLOOKUP的用法,对函数的参数进行分析。
①要寻找的关键字是 小明,因此第一个参数 (关键字) 选中 小明 单元格。
②关键字所在的栏位在 姓名 列,因此第二个参数(关键字所在栏位)选中 姓名 列。
③当小明被找到之后,我们必须指定要回传的栏位,也就是 国文 这个科目,因此第三个参数(回传的栏位)选中 国文 列。
示例:我有如下表格,现在我要根据员工姓名查询其对应的业绩。操作步骤如图:
当我更新姓名栏的内容时,对应业绩也更新。
二、XLOOKUP函数的特征
2.1 向左查询
示例:我打算从另一张表格查询每个业务员的工作单位,再回填到第一页的表格。
第二页工作表中的进行查询工作单位的表格:
填充到第一个工作表中的表格:
操作步骤如下:
①输入查询关键字。由于我是根据姓名查询出对应表格,因此查询关键字选中第一张表的姓名栏的第一个单元格(F3) 。
②由于要查询的内容在第二张表,因此XLOOKUP函数的第二和第三个函数分别选中第二张表的姓名列和业务单位列,并锁定(因为我要进行下拉,查询每个业务员的业务单位)。
效果如下:
2.2 错误提示
如果我在姓名栏中输入一个表格中不存在的名字,那么公式立即会跳出一个错误代码。
这时,我们可以为XLOOKUP添加第四个语法,错误提示。
因此,当输入正确姓名时,显示业绩信息。
输入错误姓名时,产生错误提示。
2.3 近似比对
比如今天我想根据下面这个表格,计算出每个员工可以获得的奖金。
此时可以用到近似比对功能,即XLOOKUP的第5个参数。
操作步骤如下:
效果如图:
2.4 横向查询
假设今天公司今天颁布了一个新的奖金比例,但是是横向的表格。
现在我要查询每个员工按照新的奖金比例所能得到的奖金。操作步骤与2.3节类似,只是在第二张表中按照横向选择的方向进行选中。
效果如图:
2.5 模糊查询
当我们不记得业务员的全名时,我们可以输入员工的姓,再输入乘号 *,也可以查询到业务员的业绩。操作步骤如下:
效果如图:
2.6 逆向查询
我现在想要根据单位来查询员工姓名。由于有多个员工位于不同的单位,因此根据单位查询员工姓名只返回符合条件的第一个员工的姓名。这是因为查询方向是由上而下进行数据比对。
而现在若我想查询东区里年资最浅的员工。我们可以根据XLOOKUP最后一个参数来变更查询方向。
操作如下:
效果如图:
2.7 双向筛选
现在我有如下表格,我输入季别和单位,查询对应的业绩。
操作步骤如下:
函数的第一个关键字,我们指定到单位单元格,搜寻范围为左侧的单位列。
至于要传回哪一季的资料,是由C2单元格所输入的季别来决定。因此,我们插入第二个XLOOKUP。这次我把关键字指定到C2,搜寻范围是表格顶端的季度列。回传范围则包含了所有业绩。
效果如下:
为了使用方便,我们可以在季别和单位设置下拉选单。