VLookup属于Excel查找类的函数,语法如下:
VLookup(lookup_value,table_array,col_index , range_lookup)
VLookup(查找值,查找区域,列序号,逻辑值)
- 在表格的首列查找指定数据,并返回指定数据所在行中指定列处的数据;
- 当查找值位于需查找数据区域左边第一列时,使用VLookup函数,其中“V”表示垂直方向,;
- 当查找值位于需查找数据区域上边第一行时,使用HLookup函数,其中“H”表示水平方向;
函数参数意义如下:
- Lookup_value:查找值,可以是数值、字符串或引用;
- table_array:查找区域,可以是单元格区域或区域名称;
- col_index :要返回值的列号。大于表列数返回错误值 #REF!;
- range_lookup:查找方式,分别如下:
True/1/忽略:返回近似匹配值,如果找不到精确匹配值,则返回小于lookup_value 的最大数值。要求数据升序排列;
False/0:返回精确匹配值。如果找不到,则返回错误值 #N/A
主要用途:
- 根据已知数据查找对应的另一个数据;
- 批量匹配数据,如根据工号匹配姓名;
常规应用举例:
- 单条件查询:
单条件查询对应的匹配值
- 近似匹配查询 - 考核分数转考核等级:
不需要在查询表中输入等级对应的所有可能分数,只需要输入每个等级对应的最低分数,然后使用近似匹配的VLookup
说明:
- 查询表中考核分数按升序排序;
- 没有查找到86,结果取小于86的最大值80,对应等级A;
- 使用近似匹配查找精确值:
- 针对文本字符串查找精确匹配时,VLookup函数可能较慢,可考虑使用近似匹配:
说明:
为避免不正确的结果:
- 查找表第一列按升序排序;
- Countif检查值,避免不正确的结果;
高级应用举例:
- VLookup的反向查找:
根据姓名匹配工号、年龄:
说明:
- VLookup函数只能从左向右查找,如果需要从右向左查找,则需要把区域进行“左右位置挪移”。可利用IF函数的数组效应把两列换位重新组合后,再按正常的从左至右查找;
- 在Excel参数支持数组的函数中使用数组时,返回的结果也是一个数组,使用IF后的结果返回一个数组:{"张三","P16001";"李四","P16002";"王五","P16003";"谢六","P16004";"郑七","P16007";"周八","P16008";"武九","P16009"};
- 多条件查找:
数据源:
根据部门和员工姓名查找E列的年龄:
说明:
- B73&C73 - 把两个条件连接在一起,作为一个整体进行查找;
- $A$30:$A$36&$C$30:$C$36 - 和条件连接相对应,把部门和姓名列也连接在一起,作为一个待查找的整体;
- 用IF{1,0}把连接后的两列与E列数据合并成一个两列的内存数组;
- 公式中含有多个数据与多个数据运算( $A$30:$A$36&$C$30:$C$36),所以必须以数组形式输入,即按ctrl shift后按Enter结束输入;