VLOOKUP&LOOKUP双雄战(二):LOOKUP剑走偏锋

来源:优页文档 作者:优页文档

今天是VLOOKUP与LOOKUP双雄战的第三回合。在前两个回合中,VLOOKUP旗开得胜,连胜两局。第三回合是交叉查询,LOOKUP能否展开反击止住颓势呢?

VLOOKUP和LOOKUP这对高频函数的较量注定是场持久战。在前两个回合的较量中,VLOOKUP占据上风,此番更要乘胜追击。新一轮较量,即刻开战!

ROUND 03  交叉查询


什么是交叉查询?我们可以通过一个查找值查找多个字段。如果被查找的多个字段的排列顺序与查找区域中对应字段的顺序不一致,我们称之为交叉查询。如下,我们要从数据源中查找“阿普”的多个字段“绰号”“能力”“职位”,很显然被查找字段与数据源中字段“职位”“能力”“绰号”的排列顺序不一致,这就是交叉查询,要怎么做呢?

图片

图3.1:交叉查询

最基础的做法就为每一个查找字段单独设置公式。

H3公式 =VLOOKUP($G3,$A$1:$E$12,5,0)

I3公式 =VLOOKUP($G3,$A$1:$E$12,4,0)

J3公式 =VLOOKUP($G3,$A$1:$E$12,3,0)

图片

图3.2:逐一设置查询公式

这种逐一设置公式的做法很笨拙,除了需要重复输入类似的公式外,还需要人工判别每一个单元格的返回列值。如果查找字段很多,估计会逼疯不少表亲。下面看看小花是如何使用VLOOKUP和LOOKUP做交叉查询的


VLOOKUP经天纬地,拿手好戏


VLOOKUP和MATCH这对函数组合正是为交叉查询而生。VLOOKUP通过MATCH函数的协助,自动判断出返回列值MATCH函数用于返回查找值在某一行/列中的位置,它的语法是MATCH(查找值,查找行/列,查找方式)。此处我们用到的查找方式是精确查找,第三个参数用FALSE或0表示。

图片

图3.3:MATCH的基本用法

公式说明

以B17公式为例,“职位”出现在A1:E1的第三个位置,所以MATCH的返回值为3。

介绍完MATCH函数的基本用法后,隆重介绍EXCEL函数中一种使用频率最高的函数组合——VLOOKUP+MATCH。

=VLOOKUP($G3,$A$1:$E$12,MATCH(H$2,$A$1:$E$1,0),0)

图片

图3.4:VLOOKUP+MATCH交叉查询

公式说明

VLOOKUP+MATCH组合的基本套路是=VLOOKUP(查找值,查找区域,MATCH(查找字段,字段区域,0),0)。它是在VLOOKUP的基本用法上,将第三个参数返回值列序用MATCH替换,通过匹配,自动返回目标字段在查找区域的列序。

套路的基本要点如下:

1.MATCH的查找值必须与VLOOKUP查找区域标题行中的某个单元格完全一致。这是高频错误点,需注意空格的干扰!

2.为了使公式可以拖动填充,VLOOKUP的第一个参数通常锁定列,如$G3,第二个参数通常锁定行和列,如$A$1:$E$12;MATCH的第一个参数通常锁定行,如H$2,第二个参数通常锁定行和列,如$A$1:$E$1。公式最后是“,0),0)”这样的结构,分别表示MATCH函数和VLOOKUP函数都执行精确匹配。这些细节都是小白容易忽略、出错的地方。


LOOKUP数组形式,剑走偏锋


说实话,交叉查询,LOOKUP同样无法单干,需要找帮手组团行动,譬如LOOKUP+MATCH+OFFSET。

=LOOKUP($G3,OFFSET($A$1:$A$12,,,,MATCH(H$2,$A$1:$E$1,0)))

图片

图3.5:LOOKUP的数组形式

公式说明

该公式使用了LOOKUP的数组形式=LOOKUP(查找值,查找区域),表示在查找区域的首行/列中进行匹配,返回查找区域末行/列中与之对应的值。于是问题的重点就变成了如何使查找区域的末列自动变为返回值的所在列。我们用OFFSET函数和MATCH函数来解决。

OFFSET函数是一个偏移函数,它根据给定的偏移行数和列数从初始位置偏移至指定区域,并返回指定大小的区域,它的语法是:=OFFSET(初始区域,偏移行数,偏移列数,[返回区域的行数],[返回区域的列数])

此处我们的初始区域为A1:A12,返回区域仍然是以A1:A12为首列的区域,行、列偏移量皆为0,返回区域的行数也与初始区域一致,因此这三个参数直接用逗号占位,不填数字。最后我们通过MATCH返回匹配列序数,从而确定OFFSET返回区域的列数。公式最终返回以A列为首列、以MATCH返回值为末列,包含1-12行的区域。以H3中的公式为例,MATCH返回5,则OFFSET返回结果是以A1:A12为首列的5列区域即A1:E12。把A1:E12作为LOOKUP数组形式的第二个参数,LOOKUP将查找值$G3在区域A1:E12的首列A1:A12中进行匹配,返回查找区域A1:E12的末列E1:E12中与之对应的值,从而完成交叉查询。

第三回合,在处理交叉查询问题时,VLOOKUP和LOOKUP都能应对自如

但VLOOKUP的用法较为简单,只需借助MATCH函数即可完成,而LOOKUP函数则需要MATCH和OFFSET两个函数和它配合才能实现。综合看来,后者不如前者简单易学。


资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 蓝色卡通蓝色防溺水安全教育PPT
  • 蓝色儿童安全教育学生防溺水主题PPT
  • 蓝色中小学防溺水安全教育PPT
  • 重温入党誓词党史学习教育党课PPT
  • 微党课重温入党誓词永远忠诚于党PPT
  • 入党誓词宣誓入党仪式背景PPT
  • 纪律处分条例pptPPT
  • 党课党建主题班会纪律处分条例pptPPT
  • 党课党员教育管理工作条例PPT
  • 预防溺水pptPPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2024 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。