Excel INDEX MATCH:基本和高级查找
在 Excel 中,准确检索特定数据通常是经常需要的。 虽然 INDEX 和 MATCH 函数各有其优势,但将它们组合起来可以解锁强大的数据查找工具集。 它们共同促进了一系列搜索功能,从基本的水平和垂直查找到更高级的功能,如双向、区分大小写和多标准搜索。 与 VLOOKUP 相比,INDEX 和 MATCH 的配对提供了增强的功能,允许更广泛的数据查找选项。 在本教程中,让我们深入探讨他们可以共同实现的可能性的深度。
如何在Excel中使用INDEX函数
指数 Excel 中的函数返回特定范围内给定位置的值。 INDEX 函数的语法如下:
- 排列 (required) 是指您要从中返回值的范围。
- 行数 (必需,除非 列数 存在)指的是数组的行号。
- 列数 (可选,但如果 行数 被省略)指的是数组的列号。
例如,要知道 杰夫的分数是, 6列表中的第一个学生,您可以像这样使用 INDEX 函数:
=INDEX(C2:C11,6)
√ 注:范围 C2:C11 是列出分数的地方,而数字 6 找到考试成绩 6第一个学生。
下面我们来做一个小测试。 对于公式 =索引(A1:C1,2),它会返回什么值? --- 是的,它会返回 出生日期是, 2给定行中的 nd 值。
现在我们应该知道 INDEX 函数可以完美地处理水平或垂直范围。 但是如果我们需要它返回一个包含多个行和列的更大范围内的值怎么办? 那么,在这种情况下,我们应该同时应用行号和列号。 例如,要找出 杰夫的分数 在表格范围内而不是单个列中,我们可以通过 行数 6 的网络 列数 3 ,在 从 A2 到 C11 的细胞 喜欢这个:
=INDEX(A2:C11,6,3)
- INDEX 函数可以处理垂直和水平范围。
- 如果两者 行数 和 列数 使用参数, 行数 领先于 列数,并且 INDEX 检索指定的交集处的值 行数 和 列数.
但是,对于一个非常大的多行多列的数据库,我们应用精确的行号和列号的公式肯定不方便。 而这正是我们应该结合使用 MATCH 函数的时候。
如何在Excel中使用MATCH函数
Excel 中的 MATCH 函数返回一个数值,即给定范围内特定项目的位置。 MATCH 函数的语法如下:
- Lookup_Array中 (必需)指的是要匹配的值 查找数组.
- 查找数组 (必填)是指您希望 MATCH 搜索的单元格范围。
- 比赛类型 (可选的): 1, 0 or -1.
- 1 (默认),MATCH 将找到小于或等于 Lookup_Array中. 中的值 查找数组 必须按升序排列。
- 0, MATCH 将找到第一个完全等于 Lookup_Array中. 中的值 查找数组 可以按任何顺序排列。 (对于匹配类型设置为0的情况,可以使用通配符。)
- -1, MATCH 将找到大于或等于 Lookup_Array中. 中的值 查找数组 必须按降序排列。
例如,要知道 Vera 在名单中的位置,您可以像这样使用 MATCH 公式:
=MATCH("Vera",A2:A11,0)
√ 注:结果“4”表示名称“Vera”位于列表的第 4 位。
- MATCH 函数返回查找值在查找数组中的位置,而不是值本身。
- MATCH 函数在重复的情况下返回第一个匹配项。
- 就像 INDEX 函数一样,MATCH 函数也可以处理垂直和水平范围。
- MATCH 不区分大小写。
- 如果 Lookup_Array中 MATCH 公式的内容是文本形式,请将其用引号引起来。
- 如果 Lookup_Array中 在中找不到 查找数组是, #N / A 返回错误。
现在我们已经了解了Excel中INDEX和MATCH函数的基本用法,让我们撸起袖子准备将这两个函数结合起来。
如何在 Excel 中结合使用 INDEX 和 MATCH
请参阅下面的示例以了解我们如何组合 INDEX 和 MATCH 函数:
找到 伊芙琳的分数,知道考试成绩在 3rd 栏,我们可以 使用MATCH函数自动确定行位置 无需手动计数。 随后,我们可以使用 INDEX 函数来检索 所识别的行和第三列的交叉点处的值:
=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)
好吧,由于公式可能看起来有点复杂,让我们逐一讨论一下。
指数 公式包含三个参数:
- 行数: MATCH("伊芙琳",A2:A11,0) 为 INDEX 提供值“的行位置”伊夫林“ 在范围中 A2:A11,这是 5.
- 列数: 3 指定 3INDEX 的 rd 列用于在数组中查找分数。
- 排列: A2:C11 指示 INDEX 返回指定行和列交集处的匹配值,范围为 A2 至 C11。 最后我们得到结果 90.
在上面的公式中,我们使用了一个硬编码值, “伊芙琳”。 然而,在实践中,硬编码值是不切实际的,因为每次我们寻求搜索不同的数据(例如另一个学生的分数)时都需要修改它们。 在这种情况下,我们可以利用单元格引用来创建动态公式。 例如,在这种情况下,我将 将“伊芙琳”更改为 F2:
=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)
INDEX 和 MATCH 应用双向查找
在前面的示例中,我们知道列号并使用 MATCH 公式来查找行号。 但是如果我们也不确定列号怎么办?
在这种情况下,我们可以使用两个 MATCH 函数执行双向查找,也称为矩阵查找:一个用于查找行号,另一个用于确定列号。 例如,要知道 伊芙琳的分数,我们应该使用公式:
=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))
- 第一个 MATCH 公式查找 Evelyn 在列表 A2:A11 中的位置,提供 5 作为 INDEX 的行号。
- 第二个 MATCH 公式确定分数列并返回 3 作为 INDEX 的列号。
- 该公式简化为 =索引(A2:C11,5,3),并且 INDEX 返回 90.
INDEX 和 MATCH 应用左查找
现在,让我们考虑一个需要确定 Evelyn 班级的场景。 您可能已经注意到,类列位于名称列的左侧,这种情况超出了另一个强大的 Excel 查找函数 VLOOKUP 的功能。
事实上,执行左侧查找的能力是 INDEX 和 MATCH 组合优于 VLOOKUP 的方面之一。
找到 伊芙琳的课堂,采用以下公式 在 B2:B11 中寻找伊芙琳 和 从A2:A11中检索对应的值.
=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))
请注意: 您可以使用以下命令轻松执行特定值的左查找 从右到左查找 的特点 Kutools for Excel 只需点击几下。 要实现该功能,请导航至 库工具 Excel 中的选项卡,然后单击 超级查询 > 从右到左查找 ,在 公式 组。
如果您还没有安装Kutools,请点击这里 下载并获得 30 天全功能免费试用!
INDEX 和 MATCH 应用区分大小写的查找
MATCH 函数本质上不区分大小写。 然而,当您需要公式区分大小写字符时,您可以通过合并来增强它 精确 功能。 通过在 INDEX 公式中将 MATCH 函数与 EXACT 结合起来,您可以有效地执行区分大小写的查找,如下所示:
- 排列 指的是您想要返回值的范围。
- Lookup_Array中 指的是要匹配的值,考虑到字符的大小写,在 查找数组.
- 查找数组 指的是要与 MATCH 进行比较的单元格范围 Lookup_Array中.
例如,要知道 吉米的考试成绩,使用以下公式:
=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))
√ 注意:这是一个数组公式,需要输入 按Ctrl + 转移 + 输入,Excel 365 和 Excel 2021 中除外。
- EXACT 函数比较 “吉米” 与列表中的值 A2:A11,考虑字符的大小写:如果两个字符串精确匹配,同时考虑大写和小写字符,则 EXACT 返回 TRUE; 否则,它返回 FALSE。 结果,我们得到 包含 TRUE 和 FALSE 值的数组.
- MATCH 函数然后检索 第一个 TRUE 值的位置 在数组中,应该是 10.
- 最后,INDEX 检索位于 10MATCH 在数组中提供的第一个位置。
笔记:
- 请记住按正确输入公式 Ctrl + Shift + Enter,除非你正在使用 Excel中365 or Excel中2021,在这种情况下,只需按 输入.
- 上述公式在单个列表中搜索 C2:C11。 如果您想在具有多列和行的范围内进行搜索,请说 A2:C11,您应该向 INDEX 提供列号和行号:
-
=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
- 在这个修改后的公式中,我们使用 MATCH 函数在范围内搜索“JIMMY”,考虑字符的大小写 A2:A11,一旦找到匹配项,我们就会从 3范围的第 rd 列 A2:C11.
INDEX 和 MATCH 查找最接近的匹配项
在 Excel 中,您可能会遇到需要在数据集中查找与特定值最接近或最接近的匹配项的情况。 在这种情况下,结合使用 INDEX 和 MATCH 函数以及 ABS 和 MIN 函数会非常有用。
- 排列 指的是您想要返回值的范围。
- 查找数组 指的是您想要找到最接近匹配的值的范围 Lookup_Array中.
- Lookup_Array中 指的是找到最接近匹配的值。
例如,要找出 谁的分数最接近85,使用以下公式 搜索 C85:C2 中最接近 11 分的分数 和 从A2:A11中检索对应的值.
=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))
√ 注意:这是一个数组公式,需要输入 按Ctrl + 转移 + 输入,Excel 365 和 Excel 2021 中除外。
- ABS(C2:C11-85) 计算范围内每个值之间的绝对差 C2:C11 和 85,产生绝对差异数组。
- 最小值(ABS(C2:C11-85)) 查找绝对差值数组中的最小值,它表示与 85 最接近的差值。
- 匹配功能 MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) 然后在绝对差数组中找到最小绝对差的位置,它应该是 10.
- 最后,INDEX 检索列表中该位置的值 A2:A11 对应于最接近的分数 85 范围中 C2:C11.
笔记:
- 请记住按正确输入公式 Ctrl + Shift + Enter,除非你正在使用 Excel中365 or Excel中2021,在这种情况下,只需按 输入.
- 如果出现平局,此公式将返回第一个匹配项。
- 找到 最接近平均分的匹配,替换 85 在公式中 平均值(C2:C11).
INDEX 和 MATCH 以应用具有多个条件的查找
要查找满足多个条件的值(需要跨两列或多列进行搜索),请使用以下公式。 该公式允许您通过在不同列中指定各种条件来执行多条件查找,帮助您找到满足所有指定条件的所需值。
√ 注意:这是一个数组公式,需要输入 按Ctrl + 转移 + 输入. 一对大括号将显示在公式栏中。
- 排列 指的是您想要返回值的范围。
- (查找值=查找数组) 代表单一条件。 此条件检查特定的 Lookup_Array中 匹配中的值 查找数组.
例如,找到 A班Coco的成绩,出生日期为7/2/2008,您可以使用以下公式:
=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))
笔记:
- 在这个公式中,我们避免了对值进行硬编码,从而可以通过修改单元格中的值来简单地获得具有不同信息的分数 G2, G3及 G4.
- 您应该通过按输入公式 Ctrl + Shift + Enter 除了在 Excel中365 or Excel中2021,您只需按 输入.
如果你经常忘记使用 Ctrl + Shift + Enter 要完成公式并得到不正确的结果,请使用以下稍微复杂的公式,您可以用一个简单的公式来完成 输入 键:=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
- 这些公式可能很复杂且难以记住。 要简化多条件查找而不需要手动输入公式,请考虑使用 Kutools for Excel“ 多条件查找 特征。 安装 Kutools 后,导航到 库工具 Excel 中的选项卡,然后单击 超级查询 > 多条件查找 ,在 公式 组。
如果您还没有安装Kutools,请点击这里 下载并获得 30 天全功能免费试用!
INDEX 和 MATCH 在多列中应用查找
想象一下您正在处理多个数据列的场景。 第一列充当对其他列中的数据进行分类的关键。 要确定特定条目的类别或分类,您必须在数据列中执行搜索并将其与参考列中的相关键相关联。
例如,在下表中,我们如何使用 INDEX 和 MATCH 将学生 Shawn 与其对应的班级匹配? 好吧,您可以通过公式来实现它,但公式非常广泛,并且很难理解,更不用说记住和输入了。
=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")
那是在哪里 Kutools for Excel's 多列上的索引和匹配 功能派上用场。 它简化了流程,可以快速轻松地将特定条目与其相应的类别进行匹配。 要解锁这个强大的工具并轻松地将 Shawn 与他的班级匹配起来,只需 下载并安装 Kutools for Excel 插件,然后执行以下操作:
- 选择要在其中显示匹配类的目标单元格。
- 点击 库工具 标签,点击 公式助手 > 查找和参考 > 多列上的索引和匹配.
- 在弹出的对话框中进行如下操作:
- 单击第 1 个 按钮旁边 查找列 选择包含要返回的关键信息(即类名)的列。 (此处只能选择单个列。)
- 点击第2个 按钮旁边 表_rng 选择单元格以匹配所选中的值 查找列,即学生姓名。
- 点击第三个 按钮旁边 查找值 选择包含要与班级匹配的学生姓名的单元格,在本例中为 Shawn。
- 点击 OK.
结果
Kutools 已自动生成公式,您将看到 Shawn 的班级名称立即显示在目标单元格中。
请注意: 试用 多列上的索引和匹配 功能,您需要在计算机上安装 Kutools for Excel。如果您还没有安装,请不要等待--- 立即下载并安装,享受 30 天免费试用,无任何限制。 今天就让 Excel 更智能地工作!
INDEX 和 MATCH 查找第一个非空值
要从列或行中检索第一个非空值(忽略错误),您可以使用基于 INDEX 和 MATCH 函数的公式。 但是,如果您不想忽略范围中的错误,请添加 ISBLANK 函数。
- 获取列或行中的第一个非空值,忽略错误:
-
=INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))
- 获取列或行中的第一个非空值(包括错误):
-
=INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))
笔记:
- 以上是数组公式,需要输入 按Ctrl + 转移 + 输入,Excel 365 和 Excel 2021 中除外。
- 查看本教程以获取详细说明: 获取列或行中的第一个非空值.
INDEX 和 MATCH 用于查找第一个数值
要从列或行中检索第一个数值,请使用基于 INDEX、MATCH 和 ISNUMBER 函数的公式。
=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))
笔记:
- 这是一个数组公式,需要您输入 按Ctrl + 转移 + 输入,Excel 365 和 Excel 2021 中除外。
- 查看本教程以获取详细说明: 获取列或行中的第一个数值.
INDEX 和 MATCH 用于查找 MAX 或 MIN 关联
如果需要检索与某个范围内的最大值或最小值关联的值,可以将 MAX 或 MIN 函数与 INDEX 和 MATCH 函数一起使用。
- INDEX 和 MATCH 检索与最大值关联的值:
-
=INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
- INDEX 和 MATCH 检索与最小值关联的值:
-
=INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
- 上面的公式有两个参数:
-
- 排列 指的是要返回相关信息的范围。
- 查找数组 表示要检查或搜索特定标准的一组值,即最大值或最小值。
例如,如果您想确定 谁得分最高,采用以下公式:
=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))
- 最大(C2:C11) 搜索范围内的最高值 C2:C11,这是 96.
- MATCH 函数然后查找数组中最高值的位置 C2:C11,应该是 1.
- 最后,INDEX 检索 1列表中的 st 值 A2:A11.
笔记:
- 如果有多个最大值或最小值,如上例所示,其中两个学生获得相同的最高分,则此公式将返回第一个匹配项。
- 要确定谁的得分最低,请使用以下公式:
=INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))
提示:定制您自己的 #N/A 错误消息
使用 Excel 的 INDEX 和 MATCH 函数时,如果没有匹配结果,您可能会遇到 #N/A 错误。 例如,在下表中,当尝试查找名为 Samantha 的学生的分数时,会出现 #N/A 错误,因为数据集中不存在她。
为了使电子表格更加用户友好,您可以通过将 INDEX MATCH 公式包装在 IFNA 函数中来自定义此错误消息:
=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")
笔记:
- 您可以通过替换来自定义错误消息 “未找到” 与您选择的任何文本。
- 如果您想处理所有错误,而不仅仅是#N/A,请考虑使用 IFERROR 函数而不是 干扰素:
=IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")
请注意,可能不建议抑制所有错误,因为它们会作为公式中潜在问题的警报。
以上就是Excel中INDEX和MATCH函数相关的全部相关内容。 我希望本教程对您有所帮助。 如果您想探索更多 Excel 提示和技巧, 请点击这里 访问我们广泛收集的超过数千个教程。
最佳办公效率工具
Kutools for Excel-帮助您从人群中脱颖而出
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级VLookup: 多重标准 | 多重价值 | 跨多页 | 模糊查询... | |
副词。 下拉列表: 简易下拉列表 | 依赖下拉列表 | 多选下拉列表... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 将列与 选择相同和不同的单元格 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符 ...) | 50+ 图表 类型 (甘特图 ...) | 40+ 实用 公式 (根据生日计算年龄 ...) | 19 插入 工具 (插入二维码, 从路径插入图片 ...) | 12 转化 工具 (小写金额转大写, 货币兑换 ...) | 7 合并与拆分 工具 (高级组合行, 拆分 Excel 单元格 ...) | ... 和更多 |
Kutools for Excel 拥有超过 300 个功能, 确保只需点击一下即可获得您所需要的...
Office 选项卡 - 在 Microsoft Office(包括 Excel)中启用选项卡式阅读和编辑
- 一秒钟即可在数十个打开的文档之间切换!
- 每天为您减少数百次鼠标单击,告别鼠标手。
- 查看和编辑多个文档时,可将您的工作效率提高50%。
- 为 Office(包括 Excel)带来高效的选项卡,就像 Chrome、Edge 和 Firefox 一样。