Excel数组公式，用于查找超出目标单元格的第一个匹配项 - 腾讯云开发者社区

文章/答案/技术大牛

发布

Excel公式技巧62：查找第一个和最后一个匹配的数据

学习Excel技术，关注微信公众号： excelperfect 在使用VLOOKUP函数查找数据时，如果多于一个匹配值，如何获取第一个匹配的值或者最后一个匹配的值。...将VLOOKUP函数的第4个参数忽略或指定为TRUE，即近似匹配，此时返回最后一个找到的匹配值。因为执行近似匹配查找时，Excel将找到大于查找值的值并返回该值的前一个值。...图4 在单元格F3中输入公式： =VLOOKUP(E3,$B$3:$C$7,2,TRUE) 下拉至F5。还可以使用INDEX/MATCH函数来查找多个匹配数据中的最后一个，如下图5所示。 ?...图5 在单元格F3中的公式为： =INDEX($C$3:$C$10,MATCH(E3,$B$3:$B$10,1)) 注意，公式中MATCH函数的第3个参数设置为1，执行近似匹配查找。...LOOKUP函数也能用于查找最后一个匹配值。LOOKUP函数总是执行近似匹配，公式也相当简单，如下图6所示。 ?

14.2K2 0

Excel: 使用 TEXTJOIN 与 FILTER 合并符合条件的数据

例如，传统的 VLOOKUP 函数只能返回第一个匹配项，而通过 TEXTJOIN 与 FILTER 的组合，可以将所有符合条件的记录合并输出，并用分隔符隔开。...这种方法相比传统的查找方式（如 VLOOKUP）有很大优势，前者只能返回一条结果，而后者能够一次性输出所有匹配项。...公式展示要实现上述需求，可以在目标单元格中输入以下公式： =TEXTJOIN("、", TRUE, FILTER(A:A & " (" & B:B & ")", ISNUMBER(SEARCH...ISNUMBER(SEARCH("女", C:C))：SEARCH 函数用于在性别列 C:C 中查找子串“女”，如果找到则返回一个数字位置，否则返回错误值。...最终，所有符合条件的姓名与年龄组合被串联成一条文本并显示在目标单元格中。通过上述公式，Excel 会自动将符合条件的所有结果汇总并返回到一个单元格，实现了一对多查询的效果。

2.8K1 0

您找到你想要的搜索结果了吗？

是的

没有找到

VLOOKUP很难理解？或许你就差这一个神器

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) =VLOOKUP (要查找的项、要查找位置、包含要返回的值的单元格区域中的列号...需要对应填写函数的四个参数：要查找的项：即找啥？找E5单元格的内容玉玉。要查找位置：即在哪块儿找？在部门表所在的区域B4:C9 中查找。...数组形式 INDEX(array, row_num, [column_num]) 返回由行号和列号索引选中的表或数组中元素的值。当函数 INDEX 的第一个参数为数组常量时，使用数组形式。...下面根据由数值控制钮输出的数值查找对应内容：从C14:C19区域所在的数组--省份，按照C8的数值，查找出目标省份得到查找值。...输入公式=D13=F8，并应用于=D13:I13区域内。这里输入公式中的D13是相对引用，而F8是绝对引用，意思是在应用区域内任意值等于绝对地址F8内的内容，就是符合条件，并使用此格式。

9.9K6 0

正则表达式来了，Excel中的正则表达式匹配示例

用于匹配字符串的Excel VBA正则表达式函数要在Excel中使用正则表达式，需要创建自己的函数。...匹配一个单元格中的字符串要匹配单个单元格中的字符串，在第一个参数中引用该单元格，第二个参数中包含一个正则表达式。...在第一个单元格中输入公式后，可以将其向下拖动到所有其他行，如下图1所示。...一次匹配多个单元格中的字符串要使用单个公式匹配多个字符串，在第一个参数中包含单元格区域引用： =RegExpMatch(A5:A9, “\b[A-Z]{2}-\d{3}\b”) 在支持动态数组的Excel365...中，它的工作方式是：在第一个单元格中键入公式，按Enter键，公式将自动溢出到下面的单元格中。

26.2K3 0

【收藏】数据分析必会的Excel高频函数合集

第二个参数是需要查询的单元格区域，这个区域中的首列必须是要包含查询值，否则公式将会返回错误值。如果查询区域中包含多个符合条件的查询，VLOOKUP函数只能返回第一个查找到的结果。...2.3 近似查询在实际应用中，我们往往用到的是FALSE精确匹配，无须顾虑表格是否为升序排列（Truth近似匹配容易受此影响），万一没有查询到目标，也能迅速查找原因。...3 SUMPRODUCT函数 SUMPRODUCT函数是用于在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。...=MATCH(查找的内容，查找的区域，匹配类型) 其中匹配类型包含1，0，-1 1或省略，查找小于或等于指定内容的最大值，而且指定区域必须按升序排序 0，查找等于指定内容的第一个数值 -1，查找大于或等于指定内容的最小值...=INDEX(单元格区域，指定的行数，指定的列数) 例如，以下公式，用于返回11:15单元格区域第3行和第4列交叉处的单元格值，即D13单元格。 =INDEX(A11:D15,3,4) ?

5.5K2 0

精通数组公式16：基于条件提取数据

这些顺序号解决了重复值问题，因为对于每条匹配的记录都有唯一的标识号。辅助列作为查找列，供查找函数查找并提取数据。 2.基于全数据集的数组公式。这些公式是独立的，不需要额外的列辅助。...对于这些公式，必须在公式内为与条件相匹配的记录创建一个相对位置数组。 ? 图1：需要提取两条记录，标准的查找函数对于重复值有些困难。...单独使用AND函数的问题是获得了两个TRUE值，这意味着又回到了查找列中有重复项的问题。真正想要的是查找列包含数字，其中单元格E14中第一个TRUE是数字1，而E17中第二个TRUE是数字2。 ?...图3：最终的辅助列公式使用SUM函数将AND函数的逻辑值与上方单元格中的值相加单元格H6是一个辅助单元格。在该单元格中，输入公式： =MAX(E8:E17) 得到匹配的记录的条数。...图7：AND和OR条件，双向查找从日期和商品数列中获取数据未完待续>>> 注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。

5.4K2 0

精通Excel数组公式002：谈谈公式最基本的那些事儿

文本公式，即在单元格中输出一个词语或者一段文本的公式。 4. 查找公式，即在表中查找以获取某个值。 5....数组公式，是一种高级公式，操作一组值（单元格区域）而不是单个单元格引用，或者传递多于一项的公式。数组公式能够传递计算、逻辑、文本、查找的结果。...公式的十大元素 Excel公式的组成，不外乎下列元素： 1.等号。每个公式的第一个字符一定是以等号开头。 2. 单元格引用。除了通常的单元格外，还包括定义的名称、工作表引用、工作簿引用和表。 3....例如，指定MATCH函数的第3个参数为0，表明查找时执行精确匹配。 8. 数字 9. 在双引号中的文本 10. 数组常量。例如，{1,2,3}。数学运算符在公式中，使用数学运算符执行数学运算。...Excel黄金法则如果公式输入的数据可能变化，将其放置到单元格中并使用单元格引用来引用该单元格。如果公式输入的数据不变化，则将其硬编码到公式中。

1.6K2 0

遇到复杂业务查询，怎么办？

在Excel表空白的地方，写上A机构、B机构、C机构，用于后面制作下拉列表里的值（下图）。这样做的目的是防止重复值出现在下拉列表里。...image.png 2.查询费用机构、利率档、期限的下拉列表创造好后，接下来要根据这3个条件进行费用的查询匹配。这时用到Excel里的两个查询函数：index，match。...MATCH函数就是拿要查找的值，在指定的区域进行匹配。匹配上了，就会返回目标值所在的单元格位置。...了解了以上的内容后，在费用的单元格里写下公式：【=INDEX(A:E,MATCH(I5&I6,A:A&B:B,0),MATCH(I7,A1:E1,0))】第一个MATCH公式是要同时找到机构和利率档...，属于多条件查找，涉及到数组，因此写完公式后要同时按下【ctrl+shift+enter】。

2.4K1 0

大一保姆级Excel一篇入门全——满足基本办公需求(wps版本——安装方便快捷高效)

自动换行是指在单元格中输入的文本超过单元格宽度时，Excel会自动调整文本的显示方式，将超出部分移到下一行显示，以便完整展示所有内容。换行并不会改变单元格的大小，只是在显示上进行调整。...Excel公式语法是用于在Excel中进行计算和数据处理的规则和语言。...标点符号：公式中使用的标点符号包括逗号（用于分隔参数）、冒号（用于定义范围）、括号（用于分组）等。公式通常以等号（=）开头，表示这是一个公式。...空值（Blank）：表示单元格中没有任何数据。除了以上的主要数据类型，Excel还支持其他一些特殊的数据类型，如超链接、数组、公式等。...，如何匹配成功则显示学号，匹配失败显示【#N/A】公式：=VLOOKUP(G3,A1:B21,2,FALSE) 语法格式很明确，第一个是查询匹配的内容，第二个参数是查询数据范围，第三个是要匹配的列号

1.6K6 0

你有一份面试题要查收

MATCH函数最终返回的值是查找的内容也就是姓名在表格里第几行，最终的值为1，2，3…… INDEX函数的第一参数单元格区域或数组常量,本例中就是整张表格的区域，第二参数是选择数组中的某行，如姓名 “李项...第三参数是选择数组中的某列，如工作部门在整张表的第1列，最终得到当姓名是李项时，工作部门会显示“运营部”。...【题目8】根据表一中的信息，将表二中的产品名称补充完整 image.png 按照某一条件查找匹配其他内容，通常用到 VLOOKUP公式，其语法如下： image.png 第一参数你想要查找的内容...，第二参数是要查找的位置，第三参数包含要返回的值的区域中的列号，第四参数返回近似或精确匹配-表示为 1/TRUE 或 0/假）。...MATCH 查找出指定内容在第几行 VLOOKUP 根据指定值匹配内容 image.png

3.1K1 1

精通Excel数组公式026：你弄清楚大型数组公式是怎么工作的吗？

学习Excel技术，关注微信公众号： excelperfect 在本系列中，大部分内容都是在阐述特定数组公式如何工作的逻辑，但是假设你有一个大型的数组公式，却不知道它是如何工作的，你该怎么办？...当你弄清楚并掌握后，这一切的工作都是值得的。查找包含空单元格的行中的第1个数据项下图1展示了一个数组公式，获取一行中的第1个非空单元格中的数值。...image.png 图2 查找列，在该列中匹配条件并提取数据如下图3所示，首先查找一列（“第3天”），然后在该列中匹配条件（Job 4），获取对应的员工名，并垂直显示。...注意，在右下侧列出的公式适用于Excel2010及以后的版本。 image.png 图3 查找列中最长的单词下图4展示了一个公式，用来查找一列中具有最大字符数的数据项。...image.png 图9 最后的提示数组公式不能够计算整列在Excel中，虽然可以创建非常大的数组公式，但不能创建使用整列的数组，因为重新计算使用整列单元格的数组公式是非常耗时的，Excel不允许创建这类数组

3.3K2 0

精通Excel数组公式14：使用INDEX函数和OFFSET函数创建动态单元格区域

动态单元格区域是指当添加或删除源数据时，或者随着包含单元格区域的公式被向下复制时根据某条件更改，可以自动扩展或收缩的单元格区域，可以用于公式、图表、数据透视表和其他位置。...图1：查找并求2月份的数值之和注意，图1所示的公式并不需要按Ctrl+Shift+Enter组合键，虽然INDEX函数返回的是一个单元格区域，其原因是没有执行直接数组操作。...用于处理扩大和缩小单元格区域的动态单元格区域公式在创建动态单元格区域公式之前，必须问清楚下列问题： 1.是垂直单元格区域（一列）吗？ 2.是水平单元格区域（一行）吗？...图2：对于不同数据类型查找最后一行在图2所示的公式[2]至[6]中，展示了一种近似查找值的技术：当要查找的值比单元格区域中的任何值都大且执行近似匹配（即MATCH函数的第3个参数为空）时，将总是获取列表中最后一个相对位置...注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。

11.3K1 1

手把手教你实操vlookup的7种用法，这个函数别说没用过哦！

如果查询区域中包含多个符合条件的查询值，VLOOKUP函数只能返回第一个查找到的结果。...③ 用于指定返回查询区域中第几列的值 ④ 决定函数的查找方式，如果为0或FASLE，用精确匹配方式，而且支持无序查找；如果为TRUE或被省略，则使用近似匹配方式，同时要求查询区域的首列按升序排序。...Tips： VLOOKUP函数的查询值要求必须位于查询区域中的首列，如果被查找值不在数据表的首列时，需要先将目标数据进行特殊的转换。...VLOOKUP函数在IF函数构造出的内存数组首列中查询部门职务字符串的位置，返回对应的姓名。数组公式，不要忘了按组合键。...来源：Excel之家

3.3K3 1

数据分析基础——EXCEL快速上手秘籍

这篇文章是本系列的第一篇，选择性汇总了EXCEL的常用且重点的模块和公式，用作内部员工EXCEL基础操作培训，以帮助表格基础薄弱的同事快速熟悉常用操作，提升工作效率。...这，将是后面数据分析的公式(EXCEL）基础（下一篇将会是实战篇）。文章略长，大家可以先马后看，当然更重要的是实践。...TRIM(单元格)清除的是目标单元格前后的空格，而SUBSTITUDE(单元格）清除了目标所有空格，包括字符串中间的空格。...FIND（要查找什么，TEXT，从第几个开始）和SEARCH函数都有3个参数，第一个参数是我们想要查找的内容，这里也就是“省”，第二个参数是在哪里查找，即目标单元格，我们以A28为例，第三个参数是从第几个开始查找...展开解释，首先我们想要根据G2单元格的ID——SW0001进行匹配，第一个参数就是G2，第二步，是想根据ID匹配获取表1区域的销量字段，所以在第二个参数位置输入A:C（选择A到C列所有数据），选定待匹配的数据列

3K1 0

数据分析基础——EXCEL快速上手秘籍

2.8K0 0

Excel 函数之查找和引用函数

Excel 函数之查找和引用三个函数 HLOOKUP函数 HLOOKUP函数用于在表格或数值数组的首行查找指定的数值，并在表格或数组中指定行的同一列中返回一个数值。...=HLOOKUP(查找的数值, 要查找区域，区域中行序号，匹配方式) 例1，以首行的字符为条件，查找引用已知的行号（序号）的数据 VLOOKUP函数 VLOOKUP函数用于搜索某个单元格区域的第一列，...=VLOOKUP(搜索的值，使用的区域, 区域中的列号，查找精确匹配值还是近似匹配值) 例2，以首列的字符为条件，查找引用已知的行号（序号）的数据 INDEX+MATCH函数 ★index函数的公式格式是...=index(array,row_num,column_num) =INDEX(查找区域，行号，列号) ★MATCH函数用于在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。...为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列：为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array

2.8K2 0

精通Excel数组公式022：提取唯一值列表并排序（续）

图26 下面，创建公式中的提取数据部分。如下图27所示，在单元格A11中的公式有如下元素： 1.INDEX函数的参数array包含需要查找的数据所在的单元格区域。...2.第一个MATCH函数告诉INDEX要查找的数据项的相对位置。 3.暂时将MATCH函数的参数lookup_value的值留为空。...4.指定MATCH函数的参数lookup_array的值为定义的名称HMA。 5.指定MATCH函数的参数match_type为0，进行精确匹配查找，因为有重复值。 ?...2.当公式向下复制到单元格A12中，MATCH函数需要查找数字2，从定义的名称HMA中报告相对位置4. 3.当公式复制到单元格A13中时，MATCH函数需要查找数字3，从定义的名称HMA中报告相对位置2...图30 注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。

6.4K1 0

精通数组公式17：基于条件提取数据（续）

excelperfect 导语：本文为《精通Excel数组公式16：基于条件提取数据》的后半部分。使用数组公式来提取数据创建数据提取数组公式的技巧是在公式内部创建一个“匹配记录”相对位置的数组。...图8：匹配的数据在数据集中的第7行和第10行在单元格F12中输入下面的数组公式： =IF(ROWS(F12:F12)>7,"",INDEX(A20,SMALL(IF(A11:A20>=3,IF(A11...图9：使用数组公式提取满足条件的记录对于Excel2010及以后的版本来说，还可以使用AGGREGATE函数的公式： =IF(ROWS(F12:F12)>7,"",INDEX(A20,AGGREGATE...图10：使用AGGREGATE函数的公式提取满足条件的记录示例：从一个查找值返回多个值在Excel中，诸如VLOOKUP、MATCH、INDEX等标准的查找函数不能够从一个查找值中返回多个值，除非使用数组公式...4.有两种有用的方法来考虑数据提取公式：提取匹配一组条件的记录或数据；从单个查找值返回多个数据值。注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。

4.2K1 0

VLOOKUP函数不能查找最后一个值，怎么办？

学习Excel技术，关注微信公众号： excelperfect 标签：Excel公式练习 VLOOKUP函数是使用最多的Excel函数之一，能够查找到第一个值并返回对应的值，然而，如果查找的项有多个，如何查找到最后一个值呢...举个例子，如下图1所示的数据，要查找“员工15”的最后一项工作任务。图1 下面列举几种常用的方法，供大家参考。方法1：找到要查找的最后一项任务所在的位置，并获取其值。...先将单元格区域A2:A16中的值与要查找的值（在单元格E2中）相比较，最后相同的值肯定其对应的行号最大。...或者，数组公式： =INDEX(B2:B16,MAX(ROW(A1:A16)*(A1:A16=E2))-ROW(A1)) 其原理相同，只是使用了乘法代替上面公式中的IF函数。...或者，数组公式： =INDIRECT("B"&LARGE(IF(A1:A16=E2,ROW(A1:A16)),1)) 找到要获取的值的单元格位置，使用INDIRECT函数取其值。

3.2K2 0

八种方式实现多条件匹配

之前在Excel内部的分享交流群和别的讲师探讨了多条件匹配有哪些实现方式。围观的市民刘先生表示：我活了二十多年，看见斗图的比较多，这么无聊斗Excel使用技巧的第一次见！为了更好的装逼，哦！...方法一：增加辅助列法常见的Vlookup匹配应用只能查找一个单元格，针对多条件的，就是把多个条件都放到一个单元格即可。 ?...先解释一下Vlookup的第一个参数 G2&H2就是两个单元格的合并，结果就是石原里美茂名，和刚刚创建辅助列的效果一样!...基础函数介绍 =Match（查找什么，在哪个列找，0）返回第一个参数在第二个参数中的位置 =Index（列，返回该列第几个值）返回某个列中第N个值两个组合就是Vlookup的应用咯！ ?...有上文的铺垫，我不再累述了！大大的提醒：下面的方法只适用于返回值是数字的！如果是返回值是文本，只能用上面的哦！

14.2K4 1

点击加载更多

Excel公式技巧62：查找第一个和最后一个匹配的数据

Excel: 使用 TEXTJOIN 与 FILTER 合并符合条件的数据

VLOOKUP很难理解？或许你就差这一个神器

正则表达式来了，Excel中的正则表达式匹配示例

【收藏】数据分析必会的Excel高频函数合集

精通数组公式16：基于条件提取数据

精通Excel数组公式002：谈谈公式最基本的那些事儿

遇到复杂业务查询，怎么办？

大一保姆级Excel一篇入门全——满足基本办公需求(wps版本——安装方便快捷高效)

你有一份面试题要查收

精通Excel数组公式026：你弄清楚大型数组公式是怎么工作的吗？

精通Excel数组公式14：使用INDEX函数和OFFSET函数创建动态单元格区域

手把手教你实操vlookup的7种用法，这个函数别说没用过哦！

数据分析基础——EXCEL快速上手秘籍

数据分析基础——EXCEL快速上手秘籍

Excel 函数之查找和引用函数

精通Excel数组公式022：提取唯一值列表并排序（续）

精通数组公式17：基于条件提取数据（续）

VLOOKUP函数不能查找最后一个值，怎么办？

八种方式实现多条件匹配

相关资讯

热门标签

活动推荐

运营活动

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐