如何使用 index-match 反转 excel 中的 vlookup?
要了解 INDEX 和 MATCH 如何作为反向查找公式工作,您需要选择一件简单的事情: Match 告诉索引某个值在列或行中的位置(单元格编号),然后索引返回该值。使用此位置(单元格编号)的值。
可以这样想,MATCH 函数是一名发现罪犯的卧底特工,而 INDEX 函数是一名随后逮捕该罪犯的警察。
但是,让我们详细了解如何组合这两个功能。如您所知,下面是 INDEX 的语法。
INDEX(数组, 行数, [列数])
在 INDEX 函数中,row_num 参数告诉它应该从哪一行返回值。假设如果输入 4,它将返回第 4 行的值。
要创建反向查找公式,我们需要将 MATCH 替换为 INDEX 的 row_argument。
当我们使用 MATCH 时,它会在搜索列中搜索值并返回该值的单元格编号。然后,INDEX 使用该数字来确定值列中的单元格位置。
最后它返回该单元格的值,您就得到了您正在寻找的值。但现在让我们来看一个真实的例子。下面是城市列表以及在那里工作的员工姓名。
在这里,我们需要搜索在孟买工作的员工的姓名。现在,如果您查看数据,在包含城市的列中,这是我们的查找列,在包含员工姓名的列中,这是您的值列。公式为:
=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
让我们把这个公式分成两部分来理解它。
第 1 部分:在第一部分中,我们使用 match 函数搜索值“Mumbai”,它返回“5”,这是城市列中包含值“Mumbai”的单元格的位置。
第 2 部分:在第二部分中,我们使用 INDEX 并引用员工姓名列来查找值。这里索引函数知道您想要该列的第 5 个单元格的值。因此,它在结果中返回“Siya”。
更多索引和匹配示例
我们面前有一些常见问题,我们已经使用 INDEX MATCH 公式解决了这些问题。访问示例文件:请务必从此处下载这些示例文件以遵循每个示例。
1. 使用 INDEX – MATCH 进行基本搜索
普通搜索是您需要使用搜索公式执行的最重要的任务之一,而 INDEX MATCH 非常适合此任务。这里我们有一个包含员工 ID 和姓名的数据表。每个 ID 都是唯一的,您需要通过 ID 搜索员工的姓名。
假设您要搜索名称 EMP-132。为此,公式为:
=INDEX(name_column,MATCH(emp-id,emp-id_column,0))
这就是这个公式的工作原理
首先,MATCH 匹配 emp id 列中的 emp id,并返回您要查找的 id 的单元格编号。这里的行号是 6。
之后, INDEX 使用相同的单元格编号从姓名列返回员工姓名。
2. 向左看
VLOOKUP 查找值时不能向左。正如我提到的,在 INDEX 和 MATCH 中您可以向任何方向搜索。在下面的数据表中,金额列后面有发票编号列。
因此,如果您想搜索特定账单的金额,则使用 VLOOKUP 是不可能的。在 VLOOKUP 中,当您选择一个表时,该表中的第一列将是查找列。
但是,在此表中,我们需要使用表的最后一列作为查找列。所以这里对 VLOOKUP 表示反对。让我们调用 INDEX 和 MATCH 进行救援,公式为:
=INDEX(G2:G14,MATCH(L6,J2:J14,0),0)
……这就是这个公式的工作原理
- 首先,您引用了索引函数中的金额列。这是我们需要从中获取值的列。
- 其次,在索引函数的 row_number 参数中,您使用了匹配函数并指定了发票编号,引用发票列并使用零进行精确匹配。
- 第三,匹配函数返回范围内发票的单元格编号。
最后,INDEX 使用此数字通过定位金额列中的单元格来返回金额。
3.模糊搜索
就像VLOOKUP一样,你也可以使用INDEX/MATCH进行粗略搜索。
当您要查找的值未列出并且您想要获得最接近的匹配项时,模糊搜索会很有用。在下表中,您有一个基于成绩的成绩列表。
而且,如果你想得到 79 分,你可以使用下面的公式。
=INDEX(B2:B6,MATCH(D3,A2:A6,1))
……这就是这个公式的工作原理
在此公式中,我们在 match 函数中为match_type使用 1,这允许它执行近似搜索。它返回小于或等于搜索值的第一个值。
对于 79,第一个最低值是 75,对于 75,等级是 B。这就是结果中得到 B 的原因。
4. 横向搜索
如您所知,HLOOKUP 用于水平搜索,但您也可以使用 INDEX 和 MATCH 来实现。在下面的数据表中,您有一个每月销售额的水平表,并且您想要获取“五月”的销售额。
公式为:
=INDEX(amount,0,MATCH(lookup_month,months,0))
……这就是这个公式的工作原理
在上面的公式中,我们没有在索引的 row_num 参数中使用 MATCH,而是在 column_num 中使用它。并且,match 返回五月的列号。
然后INDEX根据位置号返回结果列的值。
5. 双向搜索
在双向搜索中,我们需要从表中获取值。只需查看下表即可了解按地区和产品划分的销售额。
现在,如果您想获取特定区域的产品销售额,您需要双向搜索,为此您需要使用 INDEX MATCH MATCH 的组合。是的,这里你必须使用MATCH两次。
在 INDEX 和 MATCH 的正常组合中,您使用 MATCH 作为行号,但在双向搜索中,您还必须使用它作为列号。公式为:
=INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))
……这就是这个公式的工作原理
在上面的公式中,为了从表中获取销售额,您使用了索引函数,然后对其 row_num 和 column_num 参数使用了 match 函数。作为 column_num 参数的 match 函数返回 5 作为您引用的范围的第 5 行中的 Product-D 值。
并且,在 row_num 参数中找到的 match 函数返回 2,因为北区值位于您引用的范围的第二列中。
现在,使用这些值,索引函数返回第 2 列第 5 行中的值:1456。
6.区分大小写
如果您在列表或列中有两个相同的值但文本大小写不同时遇到问题,则可以执行区分大小写的搜索来查找正确的值。让我们看一下下面的学生列表,您在其中列出了名字,并在第二列中进行了标记。
而且,在开头,有一些名词是相同的,但在不同的文本情况下。例如,约翰·帕克和约翰·马修。假设您要搜索“JOHN”而不是“John”的品牌,您可以使用 INDEX 和 MATCH 创建精确匹配搜索。公式为:
=INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))
……这就是这个公式的工作原理
在此公式中,您在匹配函数中使用了 EXACT 函数。由于 match 函数无法搜索区分大小写的值,而 EXACT 是一个完美的函数。
它可以比较两个值,如果它们完全相同(包括大小写),则返回 TRUE,但是您必须以表格形式输入此公式,因为您需要将整个列与 EXACT 中的单个值进行比较。当你输入它时,它会返回一个像这样的数组。
=INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))
从那里您需要使用 match 函数才能从数组中获取 TRUE 的位置。
此时,您已获得需要查找的值的单元格编号(行号)。并且,您可以对此列值使用 INDEX。
重要一点:如果您通过 EXACT 获得多个 TRUE,则匹配将仅返回第一个 TRUE 的数字
7. 使用通配符和 INDEX MATCH
通配符非常有用。您可以使用通配符执行部分搜索。而且,最好的部分是,像所有其他公式搜索一样,您也可以将通配符与索引和匹配一起使用。
只需看一下下面的名单,其中就有员工的名字和姓氏以及年龄。
您需要从此列表中获取特定员工 (Sondra) 的年龄。但事实是你只知道名字。
而且,如果您使用星号,则可以使用名字搜索桑德拉的年龄。为此,公式为:
=INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)
……这就是这个公式的工作原理
星号是通配符,可以替换 n 个字符。因此,当您在名字后面使用它时,它会取代姓氏。
8. 最低值
假设您有一个学生列表,其分数如下。现在,您想要从此列表中搜索得分最低的学生的姓名。
为此,您可以使用带有索引和匹配的 MIN 函数,公式为:
=INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))
A 列中有学生的姓名,B 列中有学生的分数。
因此,当您将此公式插入单元格并按 Enter 时,它将返回分数最低的学生的姓名,即 Librada Bastian。
解释
在这个公式中,我们有三个不同的部分。
在第一部分中,MIN 函数返回最低分数。
之后,在第二部分中,匹配函数返回最低分数的单元格。
最后,索引函数使用 match 返回的相同单元格位置返回学生姓名列的值。
提示:以同样的方式,您还可以获取得分最高的学生的姓名。
9.第n次最佳成绩
现在这样想,您有一个包含考试成绩的学生列表,您希望从该列表中获取获得第二高分的学生的姓名。
问题是,你不知道第二高分是多少。
通常,当您使用公式搜索来搜索值时,您可以确定要查找的值。但是,在这里你不知道第二高分是多少。
因此,为此,您可以将一个大函数与索引结合起来并进行匹配。大函数将帮助您确定范围内的第二高值。
公式为:
=INDEX(student_names,MATCH(LARGE(score,2),score,0))
……这就是这个公式的工作原理
在此公式中,您在 match 函数内使用了 Large 函数作为 Lookup_value 参数。在大函数中,您提到了分数范围和 2 以获得第二高值。
Large 函数返回第二大值后,match 函数使用该值并返回相应的单元格编号。
最后,索引函数使用此单元格编号并返回学生姓名。
10. 多重标准
通常,索引和匹配的组合旨在搜索单个值。这就是为什么在匹配函数中只使用一个范围的原因。
但是,有时当您面对现实世界中的数据时,您需要使用多个标准来查找值。
考虑下面的例子。这里有一个产品列表,其中包含一些详细信息,例如产品名称、类别和尺寸。
您希望根据所有标准从这些数据中获取特定产品的价格。
因此,公式为:
=INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))
注意:这是一个数组公式,所以需要使用ctrl+shift+enter输入。
……这就是这个公式的工作原理
在此公式中,您有三个不同的数组来匹配三个不同的值,并且这些数组在值匹配的情况下返回 TRUE 和 FALSE。
之后,当你将它们相乘时,你会得到一个数组或类似的东西。
=INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))
match 函数返回数组中 1 的位置。
最后,INDEX 使用匹配返回的数字返回价格列的价格。
提示:如果您不想使用数组公式,可以使用SUMPRODUCT 条件。
11. 范围的第一个数值
假设您有一个列表,其中同时包含文本和数值,现在您想从该列表中获取第一个数值。
为此,您可以将 ISNUMBER 函数与索引/匹配结合起来。 ISNUMBER 可以帮助您识别哪个值是数字,哪个值是文本。
公式为:
=INDEX(list,MATCH(TRUE,ISNUMBER(list),0))
您必须以表格形式输入此公式(使用 Ctrl + Shift + Enter)。
……这就是这个公式的工作原理
在此公式中,ISNUMBER 返回一个等于列表长度的数组,在该数组中,数字值为 TRUE,其余值为 FALSE。
之后,您在匹配函数中使用 TRUE 作为搜索值。所以它返回数组中第一个 TRUE 的位置号。
最终,使用此位置编号索引返回第一个数值。
12.获取第一个非空值
让我们这样想,您有一个值列表,其中某些第一个单元格为空,并且您想要获取第一个非空值。
并且,您可以使用此公式来获取第一个非空值。
=INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))
……这就是这个公式的工作原理
我们需要将这个公式分为三个不同的部分才能明智地理解它。
首先,您在 match 函数中使用 ISBLANK 函数来获取一个数组,其中 TRUE 表示空单元格,FALSE 表示非空单元格。
其次,MATCH 返回 ISBLANK 返回的数组中第一个 TRUE 的位置号。
因此,此时您已获得第一个非空白值的单元格编号。
第三,索引函数仅返回列表中的第一个非空值。
13. 最常见的文本
现在假设给定一个文本值列表,您需要计算最常见的文本。
下面的列表中有你的名字。
但是,有些名字不止出现一次。
因此,现在您需要获取列表中出现次数最多的名称。您可以使用下面的公式,它是 MODE、INDEX 和 MATCH 的组合。
=INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))
当您插入此公式时,它将返回“Tamesha”,这是最常见的名称。
……这就是这个公式的工作原理
首先,MATCH 会将整个名称范围与其自身进行比较。并且,在此过程中,它将返回一个数组,其中每个文本都代表其第一个位置。
让我们以“Tamesha”这个名字为例,它是我们列表中最常见的名字。现在,如果您查看列表,就会发现这种情况首先发生在第 8 个单元格上,然后发生在第 12 个单元格上。
但是,如果你看一下表,对于我们有“Tamesha”的所有位置,它返回 8,这是它的第一个位置。
之后,从 MATCH 返回的数组中,mode 函数将返回出现次数最多的数字,即第一次出现“ Tamesha ”的单元格编号。
最后,INDEX 将使用此单元格编号返回文本。
14. 创建超链接
现在,假设除了搜索值之外,您还想为该值创建超链接。这样您就可以快速导航到搜索列所在的单元格。
例如,在下表中您需要获取一个人的年龄。而且,如果您为此值创建超链接,则可以轻松导航到该值所在的单元格。
并且,为此我们必须使用 HYPERLINK + Cell 以及 INDEX 和 MATCH,公式为:
这就是这个公式的工作原理
让我们把这个公式分成几个部分,以便更好地理解它。
- 首先,您在单元格函数中使用了索引和匹配。并且,当您在单元格函数中使用这两个函数时,您将获得相应值的单元格引用,而不是相应的值。
- 其次,您已将“#”与单元格引用连接起来。
- 第三,您再次使用索引和匹配来获取用作链接文本的匹配值。这样您就可以获得相应的值以及该值所在单元格的链接。