查找与引用函数vlookup、查找与引用函数有哪些 收藏 0

Excel数据查询引用，7种应用技巧，掌握2个以上的都是高手

在Excel中，使用最多的可能就是数据的查询引用，除了简单的筛选查询之外，我们还可以使用Vlookup等函数公式来实现查询引用。

一、Excel查询引用：Vlookup函数法。

功能：在指定的数据范围内查询返回符合要求的指定值。

语法结构：=Vlookup(查询值，数据范围，返回值所在的列数，匹配模式)。

其中匹配模式分为“0”和“1”两种，“0”为精准查询，“1”为模糊查询。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=VLOOKUP(H3,B3:D9,3,0)。

解读：

公式=VLOOKUP(H3,B3:D9,3,0)中，H3为查询值，B3:D3为查询的数据范围，在此范围中，目的是返回“销量”，而销量在此范围的第3列，所以第三个参数为“3”，最后一个参数“0”为精准查询。

二、Excel查询引用：Lookup函数法。

功能：从单列、单行或指定的数据区域中返回符合条件的值。

Lookup函数有两种使用形式：向量形式和数组形式。

1、向量形式。

语法结构：=Lookup(查询值,查询值所在的列,返回值所在的列)。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

1、选定数据源，以“销售员”为【主要关键字】进行【升序】排序。

2、在目标单元格中输入公式：=LOOKUP(H3,B3:B9,D3:D9)。

解读：

1、从动图的演示结果可以看出，没有对“销售员”进行排序之前，查询的结果并不准确。原因在于Lookup的“缺点”所致。所以在用Lookup函数查询引用数据时，必须先对查询值所在列的值进行升序排序。

2、公式=LOOKUP(H3,B3:B9,D3:D9)中，H3为查询值，B3:B9为查询值所在的列，D3:D9为返回值所在的列。

2、数组形式。

语法结构：=Lookup(查询值,查询值和返回值所在的数据范围)。

条件：查询值和返回值必须在数据范围的第一列和最后一列，否则无法得到正确的结果。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

1、选定数据源，以“销售员”为【主要关键字】进行【升序】排序。

2、在目标单元格中输入公式：=LOOKUP(H3,B3:D9)。

解读：

数据范围B3:D9中，B列为“查询值”H3所在的列，D列为返回值“销量”所在的列。所以数据范围以B列开始，以D列结束。

3、Lookup“改进”形式。

从“向量形式”和“数组形式”的应用中已经知道，要得到正确的查询结果，必须先以“查询值”为【主要关键字】进行【升序】排序，如果不排序，能否实现查询呢？答案当然是肯定的……

语法结构：=Lookup(1,0/查询条件,返回值范围)。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

解读：

1、公式=LOOKUP(1,0/(B3:B9=H3),D3:D9)中，查询值H3如果和B3:B9范围内的值相等，则返回。

2、其公式的本质为“数组形式”，查询值为1，0/(B3:B9=H3)形成了一个以0和False为值的新数据范围，返回值范围为D3:D9。

三、Excel查询引用：Offset+Match组合函数法。

Offset函数功能：以指定的参照为引用，通过给定的偏移量返回新的引用。

语法结构：=Offset(参照单元格,行,列,[高],[宽度])。

Match函数功能：返回定位值在指定范围中的相对位置。

语法结构：=Match(定位值,定位范围,匹配模式)。其中匹配模式有：-1（大于）、0（精准）、1（小于）三种。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=OFFSET(D2,MATCH(H3,B3:B9,0),0)。

解读：

公式中，以D2为参照单元格，用Match定位H3值在B3:B9这个范围中的相对位置，返回值作为Offset函数的第2个参数，最后用Offset提取符合条件的值。

四、Excel查询引用：Index+Match组合函数法。

Index函数功能：返回给定的单元格区域中，行列交叉处的值或引用。

语法：=Index(数据范围,行,[列])，当省略“列”时，默认为0。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=INDEX(D3:D9,MATCH(H3,B3:B9,0))。

解读：

公式=INDEX(D3:D9,MATCH(H3,B3:B9,0))中，首先用Match函数定位H3在B3:B9范围中的相对位置，作为Index函数的第2个参数，然后从D3:D9范围中返回相应位置的值。

五、Exce查询引用：Indirect+Match组合函数法。

Indirect函数功能：返回文本字符串所指定的引用。

语法结构：=Indirect(单元格引用,[引用样式])。引用样式分为：A1和R1C1样式。默认为A1样式。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=INDIRECT(\”d\”&MATCH(H3,B3:B9,0)+2)。

解读：

返回的销量在D3:D9单元格区域中，公式=INDIRECT(\”d\”&MATCH(H3,B3:B9,0)+2)中，首先用Match函数定位H3在B3:B9范围中的相对位置，以“王东”为例，则返回1，然后继续+2，暨用Indirect函数返回D3单元格的值。

六、Excel查询引用：Indirect+Address+Match组合函数法。

Address函数功能：返回指定行、列交叉单元格绝对地址。

语法结构：=Address(行,列)。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=INDIRECT(ADDRESS(MATCH(H3,B3:B9,0)+2,4))。

解读：

以“王东”为例，首先用Match函数定位其在B3:B9中的相对位置，返回值1，1+2,4作为Address函数的参数，暨返回$C$4作为Indirect的参数，最后返回值“66”。

七、Excel查询引用：Dget函数法。

功能：从数据库中返回符合条件且唯一存在的值。

语法结构：=Dget(范围数据库,返回值列数,条件数据库)。

目的：根据“销售员”的姓名查询对应的“销量”。

方法：

在目标单元格中输入公式：=DGET(B2:D9,3,H2:H3)。

解读：

Dget函数的第一个和第三个参数已经很明确的说了，是*数据库，简单的理解就是包含“标题”的数据范围。所以第一、第三个参数从B2:D9、H2:H3，而不是从B3:D9或直接的H3。

结束语：

从上述的示例中已经知道，Excel数据查询引用绝不是Lookup或Vlookup的专利，除了这两个函数外，还有很多的函数或公式都可以实现……对于使用技巧，你Get到了吗？如果在学习过程中有疑问或对Excel数据查询引用有独到的见解，欢迎亲在留言区留言讨论哦！

两种查找引用方法，INDEX+MATCH，比VLOOKUP更实用

【例一】查找编号0023对应的产品价格。

操作:在G2单元格输入公式“=INDEX(A:C,MATCH(F2,A:A,0),3)”或者公式

“=VLOOKUP(F2,A:C,3,FALSE)”，回车

备注：

（1）本例重在了解在查找引用中INDEX嵌套MATCH函数和VLOOKUP函数的共同效果的用法。

（2）本例中查找的目标值（价格）位于检索信息（编号）的右侧，所以两个函数可以达到相同的效果。也就是正向查找。

（3）如果是位于左侧呢？比如根据产品名称确定产品编码？我们看下面的例子。

【例二】查找返回产品“丙丁”对应的产品编码。

操作：在G6单元格输入公式“=INDEX(A:B,MATCH(F6,B:B,0),1)”，回车

备注：

（1）当查找的目标值（产品编码）位于检索信息（产品名称）左侧时，是无法使用VLOOKUP函数的。原因是使用VLOOKUP函数时，检索的信息（产品名称）必须位于首列。

（2）INDEX嵌套MATCH函数，是不受位置影响的。

【例三】查找返回2019年11月15日，产品“乙丙”的销售量。

操作：在G2单元格中输入公式“=INDEX(A1:AH95,MATCH(E1,A:A,0),MATCH(F1,3:3,0))”，回车。

备注：

（1）本例重在讲解INDEX嵌套双重MATCH函数，在满足行列双条件下，返回需要的数据。

（2）数据表中行数据为公司四季度的日期明细，列数据为各产品明细，数据量很大，当需要多次查找交叉数据时，用该函数能快速提高工作效率。

（3）本例无法用VLOOKUP函数一次性完成操作。

有关INDEX函数、MATCH函数和VLOOKUP函数的语法结构和基本用法，大家可以参阅之前的文章。

内容最后，欢迎大家点击关注，持续获得更多内容！

Vlookup函数的7个经典查询引用技巧，绝对的高效

查询引用，用到最多的函数为Vlookup，但你真的会用吗？其实，Vlookup函数除了常规的查询引用外，还有多种使用技巧

一、Vlookup函数：功能及语法结构。
功能：在指定的数据范围内返回符合查询要求的值。
语法：=Vlookup(查询值，数据范围，返回值列数，匹配模式)。
其中匹配模式有两种，分别为“0”或“1”。其中“0”为精准匹配，“1”为模糊匹配。
目的：查询“商品”的“销量”。
方法：
在目标单元格中输入公式：=VLOOKUP(H3,B3:C9,2,0)。

二、Vlookup函数：反向查询。
目的：根据“编码”查询“商品”名称。
方法：
1、在目标单元格中输入公式：=VLOOKUP(I3,IF({1,0},C3:C9,B3:B9),2,0)。
2、Ctrl+Shift+Enter填充。
解读：
公式中的IF({1,0},C3:C9,B3:B9)的作用为形成一个以C3:C9为第一列、B3:B9为第二列的临时数组。

三、Vlookup函数：多条件查询。
目的：根据“商品”名称和“型号”查询“销量”。
方法：
1、在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B9&C3:C9,D3:D9),2,0)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
1、当有多个查询的条件时，用连接符“&”连接在一起，对应的数据区域也用“&”连接在一起。
2、公式中IF({1,0},B3:B9&C3:C9,D3:D9)的作用为形成一个以B3:B9和C3:C9为第一列，D3:D9为第二列的临时数组。

四、Vlookup函数：多条件反向查询。
目的：根据“商品”的销售“地区”查询对应的“销量”。
方法：
1、在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
当有多个条件和数据范围时，对应的值用符号“&”连接。

五、Vlookup函数：屏蔽错误值。
目的：无查询匹配结果时，不显示错误代码#N/A， 将单元格的置空。
方法：
在目标单元格中输入公式：=IFERROR(VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0),\”\”)。
解读：
Iferror函数的作用为：判断一个表达式是否有误，如果有误，则返回本身，否则返回指定的值；语法结构为：=Iferror(表达式,表达式有误时的返回值)。

六、Vlookup函数：批量查询。
目的：根据“商品”名称批量返回相关信息。
方法：
在目标单元格中输入公式：=VLOOKUP($I3,$B$3:$F$9,MATCH(J$2,$B$2:$F$2,0),0)。
解读：
1、巧妙利用Match函数获取返回值对应的列数。
2、注意参数的引用方式，不变为“绝对”、变为“相对”，也可以是“混合引用”。

七、Vlookup函数：一对多查询。
目的：根据对应的值返回多个查询结果。
步骤1：插入辅助列。
方法：
1、在“商品”列的前面插入“辅助列”。
2、输入公式：=COUNTIF(C$3:C3,C3)。
解读：
利用Countif函数统计“商品”在对应的区域出现的次数。

步骤2：根据“商品”名称查询对应的“型号”。
方法：
1、在目标单元格中输入公式：=IFERROR(VLOOKUP($J$3&ROW(A1),IF({1,0},C3:C9&B3:B9,D3:D9),2,0),\”\”)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
公式主要运用了“逆向查询”和“屏蔽错误值”两种主要方法。

步骤3：根据“商品”名称和“型号”查询对应的销量。
方法：
1、在目标单元格中输入公式：=IFERROR(VLOOKUP($J$3&$K3,IF({1,0},C3:C9&D3:D9,E3:E9),2,0),\”\”)。
2、快捷键Ctrl+Shift+Enter填充。
解读：
公式主要应用了多条件的方法。

结束语：
文中从实际应用出发，针对不同的应用场景，对Vlookup函数的7种典型用法做了详细的解读，对于使用技巧，你Get到了吗？如果亲有更多的关于Vlookup的用法，欢迎在留言区留言讨论哦！

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com