1. 首页
  2. 自学中心
  3. 软件
  4. Excel

Excel:史上最全的VLOOKUP应用教程

欢迎加群交流excel问题

Excel:史上最全的VLOOKUP应用教程

VLOOKUP语法格式:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP(要查找的值,查找区域,要返回的结果在查找区域的第几列,精确匹配或近似匹配)

一、精确查找

根据姓名查找对应部门:

Excel:史上最全的VLOOKUP应用教程

输入公式:=VLOOKUP(G2,A:C,3,0)

G2:要查找的内容

A:C:查找区域,注意查找区域的首列要包含查找的内容

3:要返回的结果在查找区域的第3列

0:精确查找

二、近似查找

根据分数查找对应等级:

Excel:史上最全的VLOOKUP应用教程

输入公式:=VLOOKUP(B2,E:F,2,1)

B2:要查找的内容

E:F:查找区域,注意查找区域的首列要包含查找的内容

2:要返回的结果在查找区域的第2列

1:近似查找

注意查找区域中的首列内容必须以升序排序。

三、格式不一致的查找

查找数据为4的数量:

Excel:史上最全的VLOOKUP应用教程

输入公式:=VLOOKUP(D2,A:B,2,0)

D2:要查找的内容

A:B:查找区域,注意查找区域的首列要包含查找的内容

2:要返回的结果在查找区域的第2列

0:精确查找

这都没错啊,为什么结果会返回错误值#N/A呢?

细看之下你就会发现格式不一致

查找值数值型(D2单元格内容4是数值型)

查找区域文本型(A列的数据是文本型)

遇到这样的问题该怎么解决呢?

格式一致

一是可以利用分列功能将A列分列成常规,与D2单元格格式一致

Excel:史上最全的VLOOKUP应用教程
Excel:史上最全的VLOOKUP应用教程

二是可以将D2单元格内容设成文本格式,与A列格式一致

Excel:史上最全的VLOOKUP应用教程

三是变公式

Excel:史上最全的VLOOKUP应用教程

公式:=VLOOKUP(D2&””,A:B,2,0)

将查找值连接空(&””)变为文本

接下来顺便说下另一种格式不一致问题:

查找值文本型,查找区域数值型

Excel:史上最全的VLOOKUP应用教程

查找值文本型(D2单元格内容4是文本型)

查找区域数值型(A列的数据是数值型)

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(D2^1,A:B,2,0)

^1是将查找值转换成和查找区域一致的格式

转换方法多种:–、+0、-0、*1、/1…等等

四、通配符查找

根据简称查找对应应收账款:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(“*”&D2&”*”,A:B,2,0)

星号(*)匹配任意一串字符。

五、带“~”的查找

根据姓名查找对应部门:

Excel:史上最全的VLOOKUP应用教程

公式没有错,结果为什么会返回错误值#N/A呢?

因为查找内容带波形符(~)

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(SUBSTITUTE(G2,”~”,”~~”),A:C,3,0)

在查找包含通配符其本身内容时,需在通配符前键入“~”

用函数SUBSTITUTE将“~”替换成“~~”。

六、取消合并单元格

内容为数值,取消合并单元格:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(9E+307,A$2:A2,1,1)

9E+307是科学记数,表示9*10^307,是Excel允许键入的最大数值。

内容为文本,取消合并单元格:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(“座”,E$2:E2,1,1)

七、查找第一次价格

Excel:史上最全的VLOOKUP应用教程

根据物料名称查找对应第一次价格:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(F2,B:D,3,0)

当查找区域首列出现有两个或更多值与查找值匹配时,函数VLOOKUP返回第一次出现的对应值。

八、交叉查询

根据产品和地区查找对应销量:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0)

MATCH(B12,A1:G1,0)部分找到B12单元格内容“华北地区”在区域A1:G1中的位置5,把它作为VLOOKUP函数的第3参数;

公式就是:=VLOOKUP(A12,A2:G8,5,0)

查找A12单元格内容“产品D”

返回值在区域A2:G8中的第5列,即E列

即E5单元格中的值6945

九、反向查找

根据工号查找对应姓名:

Excel:史上最全的VLOOKUP应用教程

函数VLOOKUP可以借助IF{1,0}与IF{0,1}、CHOOSE{1,2}与CHOOSE{2,1}等等结构将逆序转换为顺序,从而实现查找。

函数VLOOKUP+ IF{1,0}结构:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0)

IF({1,0},B2:B11,A2:A11)部分

当为1时条件成立返回B2:B11

当为0时条件不成立返回A2:A11

可以将IF({1,0},B2:B11,A2:A11)部分抹黑按F9键查看

Excel:史上最全的VLOOKUP应用教程

就是两列顺序对换,将逆序转换为顺序

函数VLOOKUP+ IF{0,1}结构:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)

函数VLOOKUP+CHOOSE{1,2}结构:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0)

函数CHOOSE:根据给定的索引值,从参数串中选出相应值或操作。

CHOOSE(index_num, value1, [value2], …)

如果第一参数为1,则CHOOSE返回value1;如果第一参数为2,则CHOOSE返回value2。

CHOOSE({1,2},B2:B11,A2:A11)部分

当条件为1时,返回B2:B11

当条件为2时,返回A2:A11

函数VLOOKUP+CHOOSE{2,1}结构:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(D2,CHOOSE({2,1},A2:A11,B2:B11),2,0)

CHOOSE({2,1},A2:A11,B2:B11)部分

当第一参数为2时,则CHOOSE返回对应B2:B11中的值;

当第一参数为1时,则CHOOSE返回对应A2:A11中的值。

把CHOOSE({2,1},A2:A11,B2:B11)部分抹黑按F9键查看

Excel:史上最全的VLOOKUP应用教程

AB两列顺序对换,将逆序转换为顺序,再用函数VLOOKUP查找。

十、查找返回多列数据

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP($F2,$A:$D,COLUMN(B1),0),右拉填充

公式右拉返回结果在第2、3、4列

用函数COLUMN构造

COLUMN(B1)=2,公式右拉变成COLUMN(C1)、COLUMN(D1)得到3、4。

十一、按指定次数重复

Excel:史上最全的VLOOKUP应用教程

输入公式:

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),”<>”),A$2:A$5),2,0),E2)&””

按<Ctrl+Shift+Enter>三键结束

Excel:史上最全的VLOOKUP应用教程

十二、结果引用合并单元格内容

A列区域为合并单元格,根据业务员查找对应的区域:

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(“座”,OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1)

MATCH(D2,B2:B14,0)部分找到业务员“阿文”在区域B2:B14中的位置11

OFFSET(基点,偏移行数,偏移列数,行高,列宽)

OFFSET(A2,,,11)是以A2单元格为基点,偏移0行0列,返回行高为11的新区域A2:A12的引用。

OFFSET部分抹黑按F9键得到:

Excel:史上最全的VLOOKUP应用教程

用“座”等较大的汉字查找区域中最后一个单元格内容,即返回“华北地区”。

十三、有合并单元格的查找

A列产品为合并单元格,如何查找A列产品对应的单价呢?

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(VLOOKUP(“座”,A$2:A2,1,1),F:G,2,0)

Excel:史上最全的VLOOKUP应用教程

比如D5单元格公式=VLOOKUP(VLOOKUP(“座”,A$2:A5,1,1),F:G,2,0)

A$2:A5部分返回{“产品1″;”产品3”;0;0}

VLOOKUP(“座”,A$2:A5,1,1)部分用”座”查找最后一个单元格内容,即返回“产品3”

外层再套个VLOOKUP精确查找

即D5单元格公式就是=VLOOKUP(“产品3”,F:G,2,0),返回单价12

十四、与T+IF的组合应用

Excel:史上最全的VLOOKUP应用教程

输入公式:

=SUM(VLOOKUP(T(IF({1},A2:A8)),D2:E8,2,0)*B2:B8)

数组公式,按<Ctrl+Shift+Enter>三键结束

IF({1},A2:A8)部分构成三维内存数组

VLOOKUP函数第一参数不能直接为数组

函数T起降维作用,将三维引用转换为一维数组,其返回的结果仍为数组,用函数SUM求和。

十五、多条件查找

与反向查找一样,可以借助IF{1,0}与IF{0,1}、CHOOSE{1,2}与CHOOSE{2,1}等结构

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0)

数组公式,按<Ctrl+Shift+Enter>三键结束

十六、一对多查找

Excel:史上最全的VLOOKUP应用教程

输入公式:

=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$A$2:$A$11&COUNTIF(INDIRECT(“A2:A”&ROW($2:$11)),$E$2),B$2:B$11),2,),””)

数组公式,按<Ctrl+Shift+Enter>三键结束

效果图:

Excel:史上最全的VLOOKUP应用教程

十七、动态图表

【数据】→【数据验证】

Excel:史上最全的VLOOKUP应用教程

输入公式:

=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ,右拉填充

Excel:史上最全的VLOOKUP应用教程

【插入】→【插入柱形图】

操作演示:

Excel:史上最全的VLOOKUP应用教程

 

本文来自zhihu,观点不代表一起大数据-技术文章心得立场,如若转载,请注明出处:https://zhuanlan.zhihu.com/p/148938771

联系我们

在线咨询:点击这里给我发消息

邮件:23683716@qq.com

跳至工具栏