• vlookup match 电子表格/索引匹配 (SPREADSHEETS / INDEX-MATCH) In a previous article, we discussed about how and when to use VLOOKUP functions and what are the issues that we might face while using them...

    vlookup match


    In a previous article, we discussed about how and when to use VLOOKUP functions and what are the issues that we might face while using them. This article, on the other hand, will take you to a journey to understand an upgraded version of VLOOKUP. This upgrade is a combination of two functions in spreadsheets — INDEX and MATCH. Let us try and understand the working of INDEX-MATCH through the following example.

    在上一篇文章中 ,我们讨论了如何以及何时使用VLOOKUP函数,以及在使用它们时可能遇到的问题。 另一方面,本文将带您了解VLOOKUP升级版本 。 此升级是电子表格中两个功能的组合INDEX和MATCH 。 让我们尝试通过以下示例来理解INDEX-MATCH的工作。

    了解数据 (Understanding the Data)

    As always, let us take an example from our favorite data source — Kaggle. The following screenshot is a small subset of the Netflix data which consists of TV shows and movies available on Netflix as of 2019.

    与往常一样,让我们​​以我们最喜欢的数据源Kaggle为例。 以下屏幕截图是Netflix数据的一小部分,其中包括截至2019年Netflix上可用的电视节目和电影。

    Image for post
    Netflix Data for INDEX-MATCH demonstration

    This dataset consists of different shows and movies along with their unique show_id, country we are considering, date when the show was added and the year when the entity was released. It also contains rating of the show/movie, duration and title of the content piece.

    该数据集包含不同的节目和电影,以及它们唯一的show_id ,我们正在考虑的country ,添加节目的date和发布实体的year 。 它还包含内容的放映/电影ratingdurationtitle

    Consider now that we want to create a search method where the user can select a title and we display information to the user regarding that title. This search method would look something like this:

    现在考虑,我们想创建一种搜索方法,用户可以在其中选择title然后向用户显示有关该标题的信息。 此搜索方法如下所示:

    Image for post
    Example of how the search method will look like

    The user can input any title in the above example and we will try and find the type and rating of the title mentioned from the database. One of the simpler solutions to this is through VLOOKUP. We can easily find the rating of the title through it. Although, we would need to change the structure of the table to get the type of the title since VLOOKUP can only look to the right of the search value. Let’s see how can INDEX and MATCH formulas help us in solving this problem.

    用户可以在上面的示例中输入任何title ,我们将尝试从数据库中查找提到的标题的typerating 。 一种更简单的解决方案是通过VLOOKUP。 我们可以通过它轻松找到标题的等级。 虽然,我们将需要更改的结构以获取标题的类型,因为VLOOKUP 只能在搜索值的右侧查找。 让我们看看INDEX和MATCH公式如何帮助我们解决此问题。

    什么是索引? (What is INDEX?)

    INDEX formula in spreadsheets look something like this:


    Image for post
    INDEX formula in Google Spreadsheets
    Google Spreadsheets中的INDEX公式

    INDEX helps us in finding the content of the cell. It takes 3 inputs.

    INDEX帮助我们找到单元格的内容。 它需要3个输入。

    • Row: The number of rows from the beginning of the reference table where the value lies. This is an optional value. If no value is supplied, it will take the first row as the value.

      :从值所在的引用表的开头开始的行数。 这是一个可选值。 如果未提供任何值,则它将第一行作为值。

    • Column: The number of columns from the beginning of the reference table where the value lies. This is an optional value. If no value is supplied, it will take the first column as the value.

      :从值所在的参考表开始的列数。 这是一个可选值。 如果未提供任何值,则它将第一列作为值。

    To find the type of the title ‘Carrie Pilby’ in our table, we apply the following formula:

    要在我们的表格中找到标题“ Carrie Pilby”的类型,我们使用以下公式:


    We select the complete table as reference, we find that this movie title is in the 12th row and we know that the type of the title is stored in 2nd column of the reference table. This will give the result as ‘Movie’ which is absolutely correct!

    我们选择完整的表作为参考 ,我们发现该电影标题位于第12行,并且知道标题的类型存储在参考表的第二列中。 这将给出绝对正确的“电影”结果!

    But did you notice any problems with this? We actually had to count the row number and the column number to get 12 and 2 as the parameters in the formula. This isn’t easy, is it? Let’s find out if there is any other way in the world which can help us in easing this process.

    但是您注意到这个有什么问题吗? 实际上,我们必须对行号和列号进行计数,以获得12和2作为公式中的参数。 这不容易,是吗? 让我们找出世界上是否还有其他方法可以帮助我们简化这一过程。

    什么是MATCH? (What is MATCH?)

    MATCH formula in spreadsheets look something like this:


    Image for post
    MATCH formula in Google Spreadsheets
    Google Spreadsheets中的MATCH公式

    MATCH helps us in finding the relative position of the content in our table. It takes 3 inputs.

    MATCH帮助我们找到表中内容的相对位置。 它需要3个输入。

    • Search Key: The value that we want to find.

      搜索键 :我们要查找的值。

    • Range: The row/column in which the value is situated. Note that range can only take a row or a column, but not both.

      范围 :值所在的行/列。 请注意,范围只能包含一行或一列,但不能同时包含两者。

    • Search Type: For all practical purpose, we set this value as zero. This indicates that we are finding the exact value. This is an optional term which takes the value as 1 by default.

      搜索类型 :出于所有实际目的, 我们将此值设置为零 。 这表明我们正在寻找确切的值。 这是一个可选术语,默认情况下将值设为1。

    MATCH essentially gives us the row number or the column number of where the search term lies. Isn’t this the missing part of the INDEX puzzle we encountered earlier? We needed an easier way to find the row and column number of the search item, rather than counting it manually. And MATCH gives you exactly that!

    MATCH本质上为我们提供了搜索词所在的行号或列号。 这不是我们之前遇到的INDEX难题缺失部分吗? 我们需要一种更简单的方法来查找搜索项的行号和列号,而不是手动对其进行计数。 而MATCH正是为您提供!

    神圣的INDEX-MATCH婚姻 (The holy INDEX-MATCH matrimony)

    The above explanation now allows us to join the INDEX and MATCH formulas together and get the information we require with the minimum amount of hassle. Here is how a general INDEX-MATCH formula would look like:

    上面的解释现在使我们可以将INDEX和MATCH公式结合在一起,并以最少的麻烦获得所需的信息。 通用INDEX-MATCH公式如下所示:

    =INDEX(reference, MATCH(search_key, row, 0), MATCH(search_key, column, 0))

    In the above formula, we provide a reference table to the INDEX, which is basically the data table where all the information is. Next, the first MATCH formula provides the row index of the search term and the second MATCH provides the column index of the search term. Finally, the combination of these two will provide the row and column index to the INDEX formula and we’ll get our desired result! Let’s try it out in our Netflix example.

    在上面的公式中,我们提供了INDEX参考表 ,它基本上是所有信息所在的数据表。 接下来,第一个MATCH公式提供搜索项行索引, 第二个 MATCH公式提供搜索项列索引 。 最后,这两者的结合将为INDEX公式提供行索引和列索引,我们将获得理想的结果! 让我们在Netflix示例中尝试一下。

    Image for post
    INDEX-MATCH for finding content type

    The above formula selects the entire table in first parameter of INDEX. Then it searches for the movie title mentioned in K1 through MATCH formula in the entire row of content titles, which is C1:C23. This will return whatever row number the title ‘Carrie Pilby’ is in. In the second MATCH, it searches for J2, which is the parameter that we want to find, in this case Type. This will return whatever column the column name ‘Type’ is in. And together it will provide the correct result i.e. Movie.

    上面的公式在INDEX的第一个参数中选择整个表。 然后,它通过MATCH公式在整个内容标题行(即C1:C23)中搜索K1中提到的电影标题。 这将返回标题为“ Carrie Pilby”所在的行号。在第二个MATCH中,它将搜索J2,这是我们要查找的参数,在本例中为Type。 这将返回列名“ Type”所在的任何列。并且一起提供正确的结果,即Movie。

    Similarly, here is the formula for how to match rating in the table for the given content title.


    Image for post
    INDEX-MATCH for finding content rating

    与VLOOKUP比较 (Comparing with VLOOKUP)

    Often there will be comparisons on which formula to use to find the values of a given content. Although VLOOKUP is simpler to understand and provides an easy application, the INDEX-MATCH combination is a powerful match which provides the following advantages:

    通常会比较使用哪个公式来查找给定内容的值。 尽管VLOOKUP易于理解并且易于使用,但INDEX-MATCH组合是功能强大的匹配项,具有以下优点:

    1. You can use INDEX-MATCH to find a value against multiple criteria. In the above examples, we found Type and Rating with content name criteria and parameter criteria. This wouldn’t have been easy to achieve in VLOOKUP.

      您可以使用INDEX-MATCH 根据多个条件查找值 。 在以上示例中,我们找到了带有内容名称标准和参数标准的TypeRating 。 这在VLOOKUP中并非容易实现。

    2. VLOOKUP finds a match on the left and returns any value to the right of the search item. On the other hand, INDEX-MATCH can look both ways. In the above example, type was on the left of title and rating on the right, still it managed to find both the results correctly.

      VLOOKUP在左侧找到一个匹配项,并在搜索项的右侧返回任何值。 另一方面, INDEX-MATCH可以双向查看 。 在上面的示例中,类型位于标题的左侧,评级位于右侧,但仍设法正确找到了两个结果。

    Understanding INDEX-MATCH adds an extremely versatile tool in your spreadsheet armory. INDEX-MATCH along with the knowledge of Pivot Tables can really help you to improve your analytical skills. Let me know in comments if this was a helpful piece of content!

    了解INDEX-MATCH可以在电子表格库中添加一个极其通用的工具。 INDEX-MATCH以及数据透视表的知识可以真正帮助您提高分析技能。 在评论中让我知道这是否是有用的内容!

    翻译自: https://towardsdatascience.com/index-match-an-upgrade-on-vlookup-functions-320e43253d15

    vlookup match

  • 2. match函数 基本语法: match(查找对象,查找区域,匹配模式) 基本用法: 在查找区域以精确或模糊模式(0精确;1模糊)查找对象,返回对象在该区域的索引值(从1开始) 3. index函数 基本语法: index(取回...


    十二. Excel–Vlookup与match、index函数

    1. vlookup函数的不足

    • 只能从查找区域的左边开始查找
    • 只能引用单元格的值,不能引用对象,比如图片
    • 只能返回单列结构

    2. match函数

    • 基本语法: match(查找对象,查找区域,匹配模式)
    • 基本用法: 在查找区域以精确或模糊模式(0精确;1模糊)查找对象,返回对象在该区域的索引值(从1开始)

    3. index函数

    • 基本语法: index(取回区域,索引值)
    • 基本用法: 取回目标区域中下标是索引值的目标

    4. index+match替代Vlookup

    • 基本语法: index(取回区域,match(查找对象,查找区域,匹配模式))
    • 注意: 上述语法中的取回区域和查找区域一般都是相同的
    • 相比vlookup的优势:
      - 不受查找区域左右的限制
      - 可以引用图片
    • 引用图片:
      - 为Excel添加照相机功能
      - 将图片附着在单元格上(按住ALT拖拽)
      - 自定义函数:【公式】–【定义名称】–【新建函数】,函数名可以自定,比如图片,公式为index嵌套match
      - 在目标单元格中是用照相机功能,搭建好图片的底座。
      - 最后用“图片”函数索引即可

    5. 返回多列结果

    • Vlookup嵌套cloumn函数返回多列结果:
      - cloumn():返回当前单元格所在列号
      - 基本语法: Vlookup(查找对象,查找对象,cloumn(),0)
      - 限制: 目标表格必须与查找表格的表头相同且顺序一样
    • Vlookup嵌套match返回多列结果:
      - 基本语法: vlookup(查找对象,查找区域,match(目标表头,原表头,0),0)
      - 没有限制
    • 注意: 因为涉及到公式的拖拽,所以要注意单元格的引用方式,混合引用
  • vlookup函数,要查询的对象,必须在第一列。 所有,我们有时可以使用,index + match进行查询 ■效果 --- --- ・match 返回值:4 (小明在数组中,第四个) ・index 查看,数组中的第四个(match的返回值)...



    所有,我们有时可以使用,index + match进行查询




    ・match 返回值:4 (小明在数组中,第四个)

    ・index  查看,数组中的第四个(match的返回值),第1列



    vlookup() 垂直方向查找  vertical     英[ˈvɜːtɪkl]

    hlookup() 水平方向查找  horizontal     英[ˌhɒrɪˈzɒntl]



  • 本章主要内容:函数语法,与VLOOKUP比较,单元格引用原理,认识COLUMN函数,使用MatchVLOOKUP函数嵌套返回多列结果




















  • **vlookup函数用法 vlookup、hlookup、lookup函数的用法和三者的区别** lookup的用法: lookup就是查找的意思,也是称模糊查找,下面会说,用法如例: 如下图所示,=LOOKUP(B19,B2:B14,F2:F14) 的意思就是在B2:B14...
  • 文章目录index+matchvlookupsumifs...vlookup 原理是:通过识别“A”,在“A-a,B-b,C-c…“集合里找到"A"对应的值"a”,然后自动填充 参数解释:根据指定位置,选择规定区域,选择查询数据所在列数,指定精确还是模
  • match index vlookup函数都是查找引用类函数,需要查找的时候关键变量只有两个,区域+位置,区域的选择注意是否需要锁定,位置的确定可以通过输入特定的行号和列号。 match() match() 可以作为查找位置的助理...
  • 引用百家号:“INDEX MATCHVLOOKUP之间的关键区别在于VLOOKUP需要静态列引用,而INDEX MATCH使用动态列引用。动态列引用可以避免更少的错误” “较低的处理需求:VLOOKUP需要更多的Excel处理能力,因为它需要评估...
  • 使用vlookup,由于vlookup只能查询数据列右边的数据,这里就是编码必须要在名称的右边才能查询 VLOOKUP(要查询的值,查询范围,查询范围的第几列,是否近似匹配) 使用公式 =VLOOKUP(E25,B$2:C3478,2,FALSE...
  • 1. vlookup vlookup的4个参数如下: =VLOOKUP (【要查找的项】、【要查找位置】、【包含要返回的值的单元格区域中的列号】、【返回近似或精确匹配 - 指示为 1/TRUE 或 0/FALSE】) 例1: 武将表包含很多武将的详细...

    千次阅读 2020-05-01 21:19:52
    王佩丰Excle课程第十二课时笔记 MATCHINDEX函数 语法 MATCH(lookup_value,lookup_array,[match_type]) INDEX(array,row_num,[column_num])
  • 函数解析 当需要查询一行或一列并查找另一行或列中的相同位置的值时,会使用其中一个查找和引用函数LOOKUP。 使用方式 向量形式 在一行或一列中搜索值。 如果要指定包含要匹配的值的区域,请使用这种形式。 例如,...
  • 详解函数1.VLOOKUP() 一.函数清单 函数 介绍 VLOOKUP() 垂直方向查找 OFFSET() 计算偏移量 MATCH() 查找位置 INDEX() 查找数据 ROW() 引用行的数据 COLUMN() 引用列的数据 INDIRECT() 文本...
  • 当我们通过sql从数据库获得基础的数据后,...①MATCH(lookup_value,lookup_array,match-type) 参数:第1个参数为查询的值,第2个为查询的区域,第3个是查询方式,分为0(精确匹配),1(升序匹配),-1(降序匹配...
  • 一、Sumif函数 1.Sumif函数语法 sumif(range,criteria,[sum_range]) 2.Sumif函数计算数值区间 科目划分的发生额计算: 3.Sumif函数超过15位字符时的错误 类似于countif时的15位计算: 4.关于第三参数简写...
  • 一、回顾Vlookup函数 1 、 vlookup函数语法 LOOKUP (lookup_value,table_array,col_index_num,[range_lookup]) 但是vlookup函数有个短板,就是如果按照公司名称来查找客户ID的话,那它就不是从左边的字段查找...
  • 1. VLOOKUP函数语法: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) 2. index()与match() index(E:E,5) 找E列的第5个 match(要找的,去哪找,是不是精确查找)返回行号或第几个...
  • 文章目录一、HLOOKUP二、INDEXMATCH函数2.1 INDEX函数2.2 MATCH函数三、例子 一、HLOOKUP VLOOKUP也有一些缺陷,在一些情况中无法再使用VLOOKUP,如: ①当搜寻的栏位不在表格的最左侧。如此时想根据学号查找姓名...
  • VLOOKUP函数在日常工作中十分常见,以至于你要是没用过VLOOKUP函数,你都不好意思说你懂EXCEL。 一般情况下,我们需要在源数据中查找某个指定列的数据,就会用到VLOOKUP函数(如果是指定行的数据,则用HLOOKUP...
  • 1、用vlookup函数 查找以下公司的 companyId | companyFullName | |:----| | 上海云贝网络科技有限公司 | | 携程计算机技术(上海)有限公司 | | 浙江康健绿线网络技术有限公司 | | 久亿财富(北京)投资有限公司 | ...
  • vlookup函数用法示例,如何使用vlookup函数优亿在线262020-10-30在excel中提及数据查看,很多人最先想起的便是vlookup函数,由于很多人第一个触碰到的函数便是vlookup函数,主观臆断的意识让很多人感觉vlookup更为的...
  • 函数Vlookup是日常工作中常见的函数。它的本职工作是帮助你查询(返回)所需要的一个或者多个值。【注意事项与适用范围】 以单靠Vlookup函数为例,对数据区域有一定的要求 1.数据区域内不能出现错误值或者空值 2....
  • VLOOKUP函数 VLOOKUP(找什么?,在哪找?,返回第几列?,精确还是模糊?) 参数解析:以在一张表格中通过用VLOOKUP函数查找某个员工姓名返回销售额为例,第一个参数:目标单元格数据(某个姓名),第二个参数:框选...
  • vlookup函数 用于在表格查找指定的数值 =vlloku(lookup_value,table_array,col_index_num,range_lookup) 查找值 在哪找 列序数 查找的方式(精确/模糊) match函数 用于查找指定内容所匹配的单元格位置 =MATCH...
  • 1. match indexvlookup函数 和双条件查找匹配 (2天) vlookup函数用法 vlookup函数的使用方法 vlookup、hlookup、lookup函数的用法和三者的区别 EXCEL函数LookUp, VLOOKUP,HLOOKUP应用详解(含中文参数解释) ...
  • 分析:先利用Match函数根据产品名称在C列查找位置=MATCH(B13,C5:C10,0)再用Index函数根据查找到的位置从B列取值。完整的公式即为:=INDEX(B5:B10,MATCH(B13,C5:C10,0))2、双向查找【例2】如下图所示,要求根据月份和...
  • vlookup函数 查找以下公司的 companyId | companyFullName | |:----| | 上海云贝网络科技有限公司 | | 携程计算机技术(上海)有限公司 | | 浙江康健绿线网络技术有限公司 | | 久亿财富(北京)投资有限公司 | | ...
  • 在同一个excel中,有一个工作表名为test,数据为上面图片的格式,现在需要把信息表增加销量列,员工销量需要匹配业绩表中的销量,通过Vlookupyuindex+match函数匹配出并且在另一个工作表中: 新建一个工作表,复制...
  • Vlookup返回具体的值,find匹配在该单元格的第几个字符;LEN是长度。 在Excel中,MATCH函数为返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身,则应该使用 ...
  • 锦袍绣帽跃金鞍。卷旗幡。整师还。轻骑穷追,湘尾鼓声寒。千里尘清高会处,张翠幕,万人看。藕丝衫袖捧雕盘。...说到VLOOKUP函数,只要是使用Excel的,相信对它一定不陌生,它的使用好处说多少都不为过,...



1 2 3 4 5 ... 20
收藏数 1,251
精华内容 500