excel中vlookup函数使用方法 excel身份证号码显示e17( 三 )



7. 查询结果是合并单元格
在表一中 , 查找 G12 对应的平均分 , 而这个平均分是个合并单元格 。
我们只要在 H12 中输入公式:
=VLOOKUP(9E+307,OFFSET(E5,,,MATCH(G12,B5:B12,0)),1,1)
9E+307 表示 Excel 支持的最大数值 。
公式的含义是:先通过 MATCH(G12,B5:B12,0)获取到 G12 在 B5:B12 中的位置为 6 , 那么 OFFSET 函数返回的数据是 E5:E10 , 再用 VLOOKUP 的模糊查询查找并返回 。

excel中vlookup函数使用方法 excel身份证号码显示e17


如果这种方法感觉很绕 , 急忙看不明白 , 那我们换一种处理办法 。
添加一个辅助列 , 用 VLOOKUP 函数把合并单元格的列拆分成不合并的 。
在 F5 中输入公式:=VLOOKUP(9E+307,E$5:E5,1,1) , 往下填充到 F12 。
9E+307 表示 Excel 支持的最大数值 。
下来在 H12 中输入公式:=VLOOKUP(G12,B5:F12,5,0)

excel中vlookup函数使用方法 excel身份证号码显示e17


8.查询区域存在合并单元格
我们将演示数据修改成如下图 , 要查找二班李四的成绩 , 班级列存在合并单元格 , 姓名列存在重名 。

excel中vlookup函数使用方法 excel身份证号码显示e17


在 I12 中输入公式:=VLOOKUP(H12,INDIRECT(“C”&MATCH(G12,B:B,0)&”:D25″),2,0)
【excel中vlookup函数使用方法 excel身份证号码显示e17】 公式的含义是:先通过 MATCH(G12,B:B,0)获取到 G12 在 B 列中的位置为 22 , 得到字符创“C22:D25” , 然后用 INDIRECT 函数返回该字符串对应的区域数据 , 这时公式其实就变成=VLOOKUP(H12,C22:D25,2,0) , 第 2 列就是我们想返回的列 。

excel中vlookup函数使用方法 excel身份证号码显示e17


9.通配符查找
Vlookup 函数支持通配符(*和?)查找
星号表示任意一串字符 , 问号表示任意单个字符 , 
如果需要查找星号和问号本身 , 需要在星号和问号前加波形符~ , 
此时星号和问号只代表一个字符 , 不是通配符 。
想查找包含~的数据时 , 只要在~前面加波形符~ , “~~”就是查找~
因为这个比较容易理解 , 我们不举例子了
10.查找一个值 , 返回多个结果
我们将演示数据修改成如下图 , 要查找男生的成绩

excel中vlookup函数使用方法 excel身份证号码显示e17


首先我们添加一个辅助列 , 在 A18 中输入公式=(C18=$G$12)+E17 , 往下填充到 A25 , 目的是每发现一名男生 , 数字+1 。
我们只要在 H12 中输入公式:=IFERROR(VLOOKUP(ROW(A1),$A$2:$E$11,4,0),””)
往下填充公式 , 看到空值就表示查完了 。

excel中vlookup函数使用方法 excel身份证号码显示e17


11. lookup_value 和 table_array 格式不一致
lookup_value 和 table_array 格式不一致时 , 将无法返回想要的结果 , 无法修改原数据格式时 , 可以通过将 lookup_value 转换成和 table_array 一样的格式来解决 , 格式不一致有 2 种情况 。
  • lookup_value 是数值型 , table_array 是字符型
只要在 lookup_value 后面添加&”” , 就是说给 lookup_value 加上一个空字符串 , 让它变成字符串

推荐阅读