VLOOKUP函数常用套路大全

￡神魔★判官ぃ 2022-05-25 12:43 211阅读 0赞

今天和大家来说说VLOOKUP的那些事儿，深入了解一下VLOOKUP函数的各种用法，看看这位大众情人还藏着多少不为人知的秘密。  
  
函数的语法为：  
VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)  
  
第一参数是要在表格或区域的第一列中查询的值。  
第二参数是需要查询的单元格区域，这个区域中的首列必须要包含查询值，否则公式将返回错误值。如果查询区域中包含多个符合条件的查询值，VLOOKUP函数只能返回第一个查找到的结果。  
第三参数用于指定返回查询区域中第几列的值，该参数如果超出待查询区域的总列数，VLOOKUP函数将返回错误值\#REF!，如果小于1返回错误值\#VALUE!。  
第四参数决定函数的查找方式，如果为0或FASLE，用精确匹配方式，而且支持无序查找；如果为TRUE或被省略，则使用近似匹配方式，同时要求查询区域的首列按升序排序。  
  
**1、常规查询**  
如图，需要从B~E的数据表中，根据H3单元格的姓名查询对应的职务。  
公式为：  
=VLOOKUP(H3,C:E,3,0)  
![3-1G0051PJH27.jpg][]  
提示：  
VLOOKUP函数第三参数中的列号，不能理解为工作表中实际的列号，而是指定要返回查询区域中第几列的值。  
  
**2、带通配符的查询**  
如图，需要从B~E的数据表中，根据H3单元格的姓氏，查询对应的姓名和部门。  
公式为：  
=VLOOKUP($H3&"\*",$C:$E,COLUMN(A1),0)  
![3-1G0051PP4211.jpg][]  
提示：  
通配符“\*”表示任意多个字符，VLOOKUP函数第一参数使用$H3&"\*"，即在C列中查询以H2单元格内容开头的内容，并返回对应列的信息。  
  
**3、近似查询**  
如图，需要根据H~I的对照表，判断D列成绩对应的评议结果。  
公式为：  
=VLOOKUP(D2,H:I,2)  
![3-1G0051PQ9440.jpg][]  
提示：  
VLOOKUP函数第四参数被省略，在近似匹配模式下返回查询值的精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于查询值的最大值。  
使用近似匹配时，查询区域的首列必须按升序排序，否则无法得到正确的结果。  
  
**4、逆向查询**  
如图，需要从B~E的数据表中，根据H3单元格的部门，查询对应的姓名。  
公式为：  
=VLOOKUP(H3,CHOOSE(\{1,2\},D2:D11,C2:C11),2,0)  
![3-1G0051PS6428.jpg][]  
提示：  
VLOOKUP函数的查询值要求必须位于查询区域中的首列，如果被查找值不在数据表的首列时，需要先将目标数据进行特殊的转换。  
CHOOSE函数第一参数使用常量数组\{1,2\}，将查询值所在的D2:D11和返回值所在的C2:C11整合成一个新的两列多行的内存数组。  
生成的内存数组符合VLOOKUP函数的查询值必须处于数据区域中首列的要求。VLOOKUP函数以职务做查询条件，在内存数组中查询并返回对应的姓名信息，从而实现了逆向查询的目的。  
  
**5、多条件查询**  
如图，需要从B~E的数据表中，根据H3单元格的部门和I3单元格的职务，查询对应的姓名。  
J3单元格公式为：  
=VLOOKUP(H3&I3,IF(\{1,0\},D2:D11&E2:E11,C2:C11),2,)  
![3-1G0051PTV07.jpg][]  
提示：  
使用连接符“&”将部门和职务合并成新的字符串，以此作为VLOOKUP函数的查询条件。  
IF部分，先将D列的部门和E列的职务进行连接，再使用IF(\{1,0\}的方式，构造出部门职务在前、姓名在后的内存数组。  
VLOOKUP函数在IF函数构造出的内存数组首列中查询部门职务字符串的位置，返回对应的姓名。  
数组公式，不要忘了按<Ctrl+Shift+Enter>组合键。  
  
**6、一对多查询**  
如图，需要从B~E的数据表中，根据H3单元格的职务，查询对应的多个姓名。  
![3-1G0051PZN27.png][]  
首先在A2单元格输入以下公式，向下复制：  
=(E2=$H$3)+A1  
![3-1G0051P93C41.jpg][]  
然后在I3单元格输入以下公式，向下复制：  
=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")  
![3-1G0051P955A8.jpg][]  
提示：  
C列的职务每重复出现一次，A列的序号增加1。  
VLOOKUP函数使用1至N的递增序列作为查询值，使用A:C列作为查询区域，以精确匹配的方式返回与之相对应的B列的姓名。注意查找区域必须由辅助列A列开始。  
最后将辅助列字体设置为白色或进行隐藏即可。  
  
**7、按指定次数重复数据**  
如图，需要根据C列指定的次数，重复显示B列的内容。  
![3-1G0051Q00WG.png][]  
首先在A2单元格输入以下公式，向下复制：  
=A1+C2  
![3-1G0051Q022E3.png][]  
然后在E2单元格输入以下公式，向下复制：  
=IFERROR(VLOOKUP(ROW(A1),A:B,2,0),E3)&""

来源"ExcelHome"，固定链接：http://www.excelhome.net/lesson/article/excel/1872.html

[3-1G0051PJH27.jpg]: /images/20220525/98446725573440ac8ac89e997ceaf28b.png
[3-1G0051PP4211.jpg]: /images/20220525/cf29e8e7b3574a0097a6bff6ea28d251.png
[3-1G0051PQ9440.jpg]: /images/20220525/4d1b6d382ec24b36b06f8c95190a97ed.png
[3-1G0051PS6428.jpg]: /images/20220525/bf185987dbc8409486d014682a621269.png
[3-1G0051PTV07.jpg]: /images/20220525/33e87ec0f7c54a1d8110dee403dbdba2.png
[3-1G0051PZN27.png]: /images/20220525/5b2b3b8a56d34556b90554b3281223fc.png
[3-1G0051P93C41.jpg]: /images/20220525/0cf7c40a6efc4a1fbe7ab1308f398d46.png
[3-1G0051P955A8.jpg]: /images/20220525/84c1c32a31e9481cb2aadbab5f77307b.png
[3-1G0051Q00WG.png]: /images/20220525/921e772adae44bb08e44e70c780efa28.png
[3-1G0051Q022E3.png]: /images/20220525/c3d653a8698e4073aa6bd1e5fdac4954.png